MySQL DELETE: The Right Way to Remove Rows Safely

Q: Can I delete with a JOIN?

Yes, `DELETE u FROM users u JOIN orders o ON u.id = o.user_id WHERE o.created_at < '2024-01-01';`. The right answer is to use a JOIN to filter by related tables.

Q: What is the difference between DELETE and DROP?

DELETE removes rows. DROP removes the table entirely. The right answer for clearing a table is TRUNCATE, not DROP.

MySQL DELETE FROM table WHERE condition removes rows from a table. The right answer is to always use a WHERE clause, to use LIMIT to limit damage, and to wrap the delete in a transaction for safety. The team that skips the WHERE clause is on a one-way trip to the support queue.

The right DELETE syntax
The right WHERE clause
The right LIMIT
The right transaction
DELETE vs TRUNCATE
The right cascade
FAQ

The right DELETE syntax

The right syntax:

DELETE FROM users WHERE id = 42;
DELETE FROM users WHERE created_at < '2025-01-01' LIMIT 1000;

The right answer is to always use a WHERE clause. The wrong answer is DELETE FROM users; which removes every row.

The right WHERE clause

The right WHERE clause is specific. The wrong answer is DELETE FROM users WHERE 1=1; which is equivalent to removing every row. The right answer for a safe delete is to test the WHERE clause with a SELECT first:

SELECT * FROM users WHERE created_at < '2025-01-01' LIMIT 10;

If the SELECT returns the right rows, run the DELETE.

The right LIMIT

The right answer is to always use a LIMIT on a DELETE. The right pattern:

DELETE FROM logs WHERE created_at < '2025-01-01' LIMIT 10000;

If the LIMIT is hit, re-run the DELETE. The wrong answer is to remove the LIMIT and hope the WHERE clause is right.

The right transaction

The right answer is to wrap the delete in a transaction:

START TRANSACTION;
DELETE FROM users WHERE status = 'inactive';
-- verify the count is right
COMMIT;
-- or ROLLBACK if wrong

The right answer is to always run the verification query before COMMIT.

DELETE vs TRUNCATE

The right answer for a full table clear is TRUNCATE TABLE users;, not DELETE FROM users;. The right answer for a partial clear is DELETE. The wrong answer is to use TRUNCATE for partial clears — TRUNCATE removes all rows, no WHERE clause.

The right cascade

The right answer for deleting a parent and its children is a foreign key with ON DELETE CASCADE. The wrong answer is to delete the parent first and orphan the children.

FAQ

What is the difference between DELETE and TRUNCATE?

DELETE removes specific rows with a WHERE clause. TRUNCATE removes all rows. The wrong answer is to use TRUNCATE for partial deletes.

How do I recover from a wrong DELETE?

The right answer is to restore from a backup, or to use the binary log to replay. The wrong answer is to assume the data is gone.

What is the safest way to delete?

The right answer is to start a transaction, run the DELETE, verify the count, then COMMIT. The wrong answer is to run the DELETE without a transaction.

Can I delete with a JOIN?

Yes, DELETE u FROM users u JOIN orders o ON u.id = o.user_id WHERE o.created_at < '2024-01-01';. The right answer is to use a JOIN to filter by related tables.

What is the difference between DELETE and DROP?