MySQL DELETE

The DELETE statement removes existing rows from a table.

DELETE FROM students
WHERE id = 5;

This removes the student whose ID is 5.

The basic syntax is:

DELETE FROM table_name
WHERE condition;

The WHERE condition identifies the records that should be deleted.

A primary key or unique ID is useful for deleting one specific record.

DELETE FROM students
WHERE id = 10;

Only the record with ID 10 is deleted.

WHERE allows you to select specific rows for deletion.

DELETE FROM students
WHERE course = 'PHP';

This deletes all students whose course is PHP.

If WHERE is omitted, DELETE removes all rows from the table.

DELETE FROM students;

The table structure remains, but all records are removed.

Multiple conditions can be combined using AND.

DELETE FROM students
WHERE course = 'Python'
AND age < 18;

This deletes Python students who are younger than 18.

OR can be used when either condition should match.

DELETE FROM students
WHERE course = 'PHP'
OR course = 'JavaScript';

This deletes students from either PHP or JavaScript.

IN is useful when several values need to be deleted.

DELETE FROM students
WHERE course IN ('PHP', 'JavaScript');

This deletes records whose course matches either PHP or JavaScript.

NOT IN can delete records that do not belong to a specified list.

DELETE FROM students
WHERE course NOT IN ('Python', 'Java');

This deletes students whose course is neither Python nor Java.

LIKE can be used to delete records matching a text pattern.

DELETE FROM students
WHERE name LIKE 'Test%';

This deletes students whose names start with Test.

BETWEEN can be used to delete records within a range.

DELETE FROM students
WHERE age BETWEEN 18 AND 20;

This deletes students whose age is between 18 and 20, including the boundary values.

IS NULL can be used to delete records containing missing values.

DELETE FROM students
WHERE email IS NULL;

This deletes students whose email is NULL.

IS NOT NULL can also be used as a DELETE condition.

DELETE FROM students
WHERE email IS NOT NULL;

This deletes all records where email contains a non-NULL value.

Comparison operators can be used in the WHERE condition.

DELETE FROM students
WHERE fee < 5000;

This deletes students whose fee is less than 5000.

DELETE FROM students
WHERE course = 'Python'
AND age > 25
AND fee < 10000;

This deletes only records satisfying all three conditions.

MySQL allows LIMIT with DELETE to restrict the number of rows removed.

DELETE FROM students
WHERE course = 'Test'
LIMIT 5;

This deletes up to five matching records.

MySQL allows ORDER BY and LIMIT together with DELETE.

DELETE FROM students
WHERE course = 'Test'
ORDER BY id ASC
LIMIT 5;

This selects the earliest five matching records according to id and deletes them.

A single DELETE statement can remove many rows.

DELETE FROM students
WHERE course = 'Python';

If ten records match the condition, all ten matching records are deleted.

DELETE can use a subquery when records need to be selected based on another query.

DELETE FROM students
WHERE course IN (
    SELECT course_name
    FROM courses
    WHERE status = 'Inactive'
);

This concept allows records to be deleted based on information from another table.

MySQL also supports deleting records based on a related table using JOIN syntax.

DELETE s
FROM students AS s
INNER JOIN courses AS c
ON s.course = c.course_name
WHERE c.status = 'Inactive';

This deletes matching student records whose related course is inactive.

A safe DELETE workflow is:

1. Write the condition.
2. Run SELECT using the same condition.
3. Review the records returned.
4. Run DELETE only after verification.
5. Run SELECT again to confirm the deletion.

SELECT *
FROM students
WHERE id = 10;

DELETE FROM students
WHERE id = 10;

SELECT *
FROM students
WHERE id = 10;

When using transactional storage engines such as InnoDB, DELETE can be part of a transaction.

START TRANSACTION;

DELETE FROM students
WHERE id = 10;

ROLLBACK;

ROLLBACK can undo the DELETE if the transaction has not been committed.

If the deletion is correct, the transaction can be committed.

START TRANSACTION;

DELETE FROM students
WHERE id = 10;

COMMIT;

COMMIT permanently applies the transaction according to the transaction rules of the storage engine.

• Forgetting the WHERE clause
• Using the wrong WHERE condition
• Deleting more rows than intended
• Confusing DELETE with DROP TABLE
• Confusing DELETE with TRUNCATE
• Not checking records with SELECT first
• Using NOT IN without considering NULL values
• Deleting production data without a backup or recovery plan

-- UPDATE
UPDATE students
SET fee = 20000
WHERE id = 1;

-- DELETE
DELETE FROM students
WHERE id = 1;

Suppose a student with ID 15 has left the institute.

SELECT *
FROM students
WHERE id = 15;

DELETE FROM students
WHERE id = 15;

SELECT *
FROM students
WHERE id = 15;

The first SELECT verifies the record, DELETE removes it, and the final SELECT checks whether it still exists.

During development, test records can be removed using a suitable condition.

SELECT *
FROM students
WHERE name LIKE 'Test%';

DELETE FROM students
WHERE name LIKE 'Test%';

Using SELECT first helps verify exactly which records will be deleted.

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    course VARCHAR(100),
    age INT,
    email VARCHAR(150),
    fee DECIMAL(10,2)
);

INSERT INTO students
(name, course, age, email, fee)
VALUES
('Amit', 'Python', 22, 'amit@gmail.com', 15000.00),
('Priya', 'Java', 21, 'priya@gmail.com', 18000.00),
('Rahul', 'PHP', 24, 'rahul@gmail.com', 12000.00),
('Neha', 'Python', 20, NULL, 15000.00),
('Test User', 'Test', 18, 'test@example.com', 5000.00);

SELECT *
FROM students
WHERE course = 'Test';

DELETE FROM students
WHERE course = 'Test';

SELECT *
FROM students;

The first SELECT checks the test record. DELETE removes the matching record, and the final SELECT displays the remaining students.