SQL DELETE Statement

The SQL DELETE statement is used to remove one or more records from a table. Unlike the UPDATE statement, which modifies existing records, the DELETE statement permanently removes rows based on a specified condition.

In this tutorial, we will explain the syntax, usage, and various examples of the DELETE statement.

Syntax of SQL DELETE Statement

The basic syntax of an SQL DELETE statement is as follows:

</>
Copy
DELETE FROM table_name
WHERE condition;

Each part of this syntax has a specific purpose:

  • DELETE FROM: Specifies the table from which you want to delete data.
  • table_name: The name of the table where records will be deleted.
  • WHERE: Filters the rows to be deleted. Without a WHERE clause, all rows in the table will be deleted, which should be used with caution.

For safety, it’s best to always use a WHERE clause with DELETE to avoid accidental deletion of all records in the table.

Step-by-Step Examples with MySQL

We’ll go through various examples demonstrating the DELETE statement in MySQL. Using MySQL 8.0 with MySQL Workbench, we’ll use a sample students table with fields id, name, age, grade, and locality.

Setup for Examples: Creating the Database and Table

1. Open MySQL Workbench and create a new database:

</>
Copy
CREATE DATABASE school;

2. Select the school database:

</>
Copy
USE school;

3. Create a table named students:

</>
Copy
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    grade VARCHAR(10),
    locality VARCHAR(50)
);

4. Insert some sample records to use in our examples:

</>
Copy
INSERT INTO students (name, age, grade, locality)
VALUES
('Alice', 14, '8th', 'Northside'),
('Bob', 15, '9th', 'Westend'),
('Charlie', 14, '8th', 'Northside'),
('David', 16, '10th', 'Southend'),
('Eva', 15, '9th', 'Westend');

Examples: Deleting Records in the Table

Now, let’s explore different scenarios of deleting records in this table.

SQL DELETE - Table for Example

1. Deleting a Single Row Based on a Condition

To delete the row of the student with id = 2:

</>
Copy
DELETE FROM students
WHERE id = 2;

This query deletes the record for the student named Bob with ID 2.

SQL DELETE - Deleting a Single Row Based on a Condition

After the delete operation, the table contents are as shown in the following.

SQL DELETE - Table after delete operation

2. Deleting Multiple Rows Using a Condition

To delete all students in the “9th” grade:

</>
Copy
DELETE FROM students
WHERE grade = '9th';

This query deletes all records where the grade is “9th”. Both Bob and Eva, who are in 9th grade, will be deleted.

After the delete operation, the contents of the table are as shown in the following.

SQL DELETE - Deleting Multiple Rows Using a Condition

3. Deleting Rows Based on Multiple Conditions

To delete students who are 14 years old and live in “Northside”:

</>
Copy
DELETE FROM students
WHERE age = 14 AND locality = 'Northside';

This query deletes all students who match both conditions: 14 years of age and locality “Northside”. This affects Alice and Charlie.

SQL DELETE - Deleting Rows Based on Multiple Conditions

4. Deleting All Rows in a Table

To delete all records from the students table:

</>
Copy
DELETE FROM students;

This query removes all rows from the students table, effectively clearing the table. Use with caution as this is irreversible.

SQL DELETE - Deleting All Rows in a Table

5. Deleting Rows Using a Subquery

To delete students who are younger than the average age of students:

</>
Copy
DELETE FROM students
WHERE age < (SELECT AVG(age) FROM students);

This query uses a subquery to find the average age and deletes all students whose age is less than this average.

FAQs for SQL DELETE

1. What does the SQL DELETE statement do?

The SQL DELETE statement is used to permanently remove one or more rows from a table. It requires a WHERE condition to specify which rows to delete; otherwise, it deletes all rows.

2. Can I delete multiple rows with one SQL DELETE statement?

Yes, you can delete multiple rows by specifying a condition that matches more than one row in the WHERE clause.

3. What happens if I use SQL DELETE without a WHERE clause?

If you omit the WHERE clause, the DELETE statement will delete all rows in the table. This action is irreversible, so use it carefully.

4. Can I use a subquery with SQL DELETE?

Yes, subqueries can be used with DELETE to create complex conditions, such as deleting rows based on calculations or data from other tables.

5. Is there a way to delete data without deleting the table structure?

Yes, the DELETE statement removes data from the table without affecting its structure. The table remains intact, ready for new data insertion.