DELETE Statement in SQL

SQL

The DELETE statement allows us to remove records from a table based on specified conditions. It is generally used to remove rows from the Table.

Syntax

The DELETE statement in SQL follows a straightforward syntax:

DELETE FROM table_name
WHERE condition;

DELETE FROM: Specifies the action to delete records from a table.
table_name: The name of the table from which records will be deleted.
WHERE condition: Specifies the conditions that must be met for a record to be deleted. If omitted, all records from the table will be deleted.

Use Case Scenario

Consider a scenario where you have a ‘students’ table in a database, and you want to remove a student who has completed their course. The DELETE statement allows you to achieve this selectively.

Practical Example

Suppose the ‘students’ table looks like this:

student_id	first_name	last_name	age	course
1	John	Doe	22	Computer Sci
2	Jane	Smith	21	Biology
3	Bob	Johnson	23	Mathematics

Suppose we want to remove the student with student_id = 2. The SQL query is:

DELETE FROM students
WHERE student_id = 2;

Output

After executing the DELETE query, our ‘students’ table transforms to:

student_id	first_name	last_name	age	course
1	John	Doe	22	Computer Sci
3	Bob	Johnson	23	Mathematics

Let’s explore a few more examples of using the SQL DELETE statement with different scenarios:

Example 1: Deleting Records Without a WHERE Clause

Deleting all records from the ‘students’ table without a WHERE clause will remove all data.

-- Delete all records from the 'students' table
DELETE FROM students;

Output: After executing this query, the ‘students’ table would be empty.

Example 2: Deleting Records Based on a Condition

Deleting students who are not enrolled in any course.

-- Delete students who are not enrolled in any course
DELETE FROM students
WHERE course IS NULL;

Output: The ‘students’ table will be updated, removing records where the ‘course’ column is NULL.

Example 3: Deleting Records Based on Multiple Conditions

Deleting students who are both older than 25 and enrolled in the ‘History’ course.

-- Delete students older than 25 and enrolled in 'History'
DELETE FROM students
WHERE age > 25 AND course = 'History';

Output: The ‘students’ table will be updated, removing records that meet both conditions.

Example 4: Deleting Specific Records Using IN Clause

Deleting students with specific student IDs.

-- Delete students with specific IDs
DELETE FROM students
WHERE student_id IN (2, 4, 6);

Output: The ‘students’ table will be updated, removing records with the specified student IDs.

Example 5: Deleting Duplicate Records

Deleting duplicate records from the ‘students’ table based on the ‘first_name’ and ‘last_name’ columns.

-- Delete duplicate records from the 'students' table
DELETE FROM students
WHERE student_id NOT IN (
    SELECT MAX(student_id)
    FROM students
    GROUP BY first_name, last_name
);

Output: The ‘students’ table will be updated, keeping only the record with the highest ‘student_id’ for each unique combination of ‘first_name’ and 'last_name’.