MySQL DELETE Statement

Last updated: October 19, 2026Oct 19, 2026

The DELETE statement removes rows from a table: expired sessions, cancelled orders, spam accounts. It is the "D" in CRUD. Deleting is easy, and so is deleting the wrong rows, so this tutorial pairs every technique with a way to stay safe.

1. Syntax

DELETE FROM table_name
[WHERE condition]
[ORDER BY ...] [LIMIT n];

2. Deleting Matching Rows

Always preview with SELECT, then delete with the same WHERE:

SELECT * FROM sessions WHERE expires_at < '2026-03-01';

Preview: rows that will be deleted

DELETE FROM sessions WHERE expires_at < '2026-03-01';

Query OK, 2 rows affected

SELECT * FROM sessions;

Remaining rows

3. Deleting One Row by Key

DELETE FROM sessions WHERE id = 6;

Query OK, 1 row affected

Deleting by primary key is the safest form: it can never touch more than one row. If nothing matches, MySQL simply reports 0 rows affected:

DELETE FROM sessions WHERE id = 999;

Query OK, 0 rows affected

4. ORDER BY and LIMIT

Delete only the oldest N rows, or delete a huge table in small batches to keep locks short:

DELETE FROM sessions ORDER BY expires_at LIMIT 1;
SELECT * FROM sessions;

Result: 2 rows

-- batch pattern: repeat until 0 rows affected
DELETE FROM audit_log WHERE created_at < '2025-01-01' ORDER BY id LIMIT 10000;

5. DELETE with JOIN

Delete rows based on another table. Remove sessions of banned users:

INSERT INTO sessions VALUES (10, 7, '2026-05-01'), (11, 7, '2026-05-02');

DELETE s
FROM sessions s
JOIN users u ON u.user_id = s.user_id
WHERE u.status = 'banned';

SELECT * FROM sessions ORDER BY id;

Result: 2 rows

Name the table(s) to delete from right after DELETE: DELETE s, u FROM … would delete from both.

6. DELETE with a Sub-query

INSERT INTO sessions VALUES (12, 42, '2026-06-01');   -- user 42 doesn't exist

DELETE FROM sessions
WHERE user_id NOT IN (SELECT user_id FROM users);

SELECT * FROM sessions ORDER BY id;

Result: 2 rows

7. Deleting Duplicate Rows

The contacts table has repeated emails. Keep the earliest row (lowest id) of each email:

SELECT email, COUNT(*) AS copies FROM contacts GROUP BY email HAVING COUNT(*) > 1;

Result: 2 rows

DELETE c1
FROM contacts c1
JOIN contacts c2 ON c2.email = c1.email AND c2.id < c1.id;

SELECT * FROM contacts ORDER BY id;

Result: 3 rows

8. Foreign Keys and DELETE

If other tables reference the rows you delete, the foreign key rule decides what happens: RESTRICT blocks the delete, CASCADE deletes the child rows too, SET NULL unlinks them. See MySQL Foreign Key.

CREATE TABLE posts (post_id INT PRIMARY KEY, user_id INT,
  FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE);
INSERT INTO posts VALUES (100, 9), (101, 9), (102, 3);
DELETE FROM users WHERE user_id = 9;
SELECT * FROM posts;

Result: 1 row

Divya's two posts were deleted with her account.

9. Undoing a DELETE

Inside a transaction, DELETE can be rolled back:

START TRANSACTION;
DELETE FROM users;                          -- oops, no WHERE
SELECT COUNT(*) AS users_left FROM users;
ROLLBACK;
SELECT COUNT(*) AS users_left FROM users;

Inside the transaction

After ROLLBACK

10. Soft Deletes

Many applications never physically delete important rows. They mark them as deleted instead, keeping history and allowing "undo":

ALTER TABLE users ADD COLUMN deleted_at DATETIME NULL;
UPDATE users SET deleted_at = '2026-03-25 10:00:00' WHERE user_id = 7;
SELECT user_id, name FROM users WHERE deleted_at IS NULL;

Result: 2 rows

Queries must then filter on deleted_at IS NULL; a view of active rows makes that easy.

11. DELETE vs TRUNCATE vs DROP

12. Practice Exercises

DELETE FROM sessions WHERE expires_at >= '2026-03-01' AND expires_at < '2026-04-01';
SELECT * FROM sessions;

Query OK, 2 rows affected

DELETE FROM users
WHERE deleted_at IS NULL
  AND user_id NOT IN (SELECT user_id FROM sessions);
SELECT user_id, name, deleted_at FROM users;

Query OK, 2 rows affected

13. Summary

• DELETE FROM t WHERE …; removes matching rows; without WHERE it removes all rows.
• Preview with SELECT, delete by primary key where possible, and use transactions for risky deletes.
• ORDER BY + LIMIT deletes a few rows or works in batches.
• Multi-table DELETE … JOIN and sub-queries delete based on other tables.
• Foreign key rules may block or cascade deletes; consider soft deletes for important data.