SQL DELETE Row(s) from TABLE

SQL DELETE Statement

The SQL DELETE statement is used to remove existing records from a table. This statement allows you to delete specific rows based on a condition or all rows in a table.

---

Syntax

DELETE FROM table_name
WHERE condition;

The DELETE statement has the following components:

• table_name: The name of the table from which to delete the data.
• condition: The condition for selecting rows to delete. If omitted, all rows in the table will be deleted.

---

Example SQL DELETE Statement Queries

Let's look at some examples of SQL DELETE statement queries:

1. Basic DELETE Example

DELETE FROM employees
WHERE id = 1;

This query deletes the row from the employees table where the id is 1. The result will be that the row with id 1 is removed from the table.

2. DELETE Multiple Rows

DELETE FROM employees
WHERE last_name = 'Smith';

This query deletes all rows from the employees table where the last_name is 'Smith'. The result will be that all rows with the last name 'Smith' are removed from the table.

3. DELETE All Rows

DELETE FROM employees;

This query deletes all rows from the employees table. The result will be that the employees table is emptied of all its data.

---

Full Example

Let's go through a complete example that includes creating a table, inserting data, deleting data, and querying the table.

Step 1: Creating a Table

This step involves creating a new table named employees to store employee data.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

In this example, we create a table named employees with columns for id, first_name, last_name, and email.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the employees table.

INSERT INTO employees (first_name, last_name, email)
VALUES ('John', 'Doe', 'john.doe@example.com');

INSERT INTO employees (first_name, last_name, email)
VALUES ('Jane', 'Smith', 'jane.smith@example.com');

INSERT INTO employees (first_name, last_name, email)
VALUES ('Jim', 'Brown', 'jim.brown@example.com');

Here, we insert data into the employees table.

Step 3: Deleting Data from the Table

This step involves deleting some data from the employees table.

DELETE FROM employees
WHERE id = 2;

Here, we delete the row with id 2 from the employees table.

Step 4: Querying the Table

This step involves selecting the data from the employees table to view the remaining records.

SELECT *
FROM employees;

This query retrieves all the rows from the employees table. The result will be:

id  first_name  last_name  email
--- ----------- ---------- ------------------------
1   John        Doe        john.doe@example.com
3   Jim         Brown      jim.brown@example.com

Conclusion

The SQL DELETE statement is a powerful tool for removing existing records from a table. Understanding how to use the DELETE statement and its syntax is essential for effective data management and manipulation in SQL databases.