DELETE
Removing rows safely: test the WHERE with a SELECT first, delete in bounded chunks, and know when TRUNCATE is not a substitute.
DELETE [IGNORE] FROM tbl_name [[AS] alias] [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]DELETE removes whole rows. There is no undo outside a transaction, and a DELETE with no WHERE empties the table, so the discipline around the statement matters more than the statement itself.
Test the predicate first
Write the SELECT before the DELETE: same table, same WHERE. Read the rows it returns. Then change the leading SELECT ... to DELETE and run it unchanged. This one habit catches the missing AND, the NULL that never compares equal, and the join that matched more rows than you expected.
Single-table and multiple-table forms
The single-table form accepts ORDER BY and LIMIT, which is how you delete a large set in survivable chunks. The multiple-table forms — DELETE t1, t2 FROM ... and DELETE FROM t1, t2 USING ... — delete matched rows from several joined tables at once, and accept neither ORDER BY nor LIMIT.
What trips people up
As with UPDATE, you cannot select from the table you are deleting from inside a subquery. Deleting millions of InnoDB rows in one statement can exceed the lock table size; the manual's own suggestion for emptying most of a huge table is to SELECT the survivors into a new table, RENAME TABLE, then drop the original. Finally, DELETE does not reset an AUTO_INCREMENT counter for InnoDB or MyISAM — TRUNCATE TABLE does.
Example
-- Step 1: read the rows before removing them. Same table, same predicate.
SELECT id, email, deleted_at
FROM users
WHERE deleted_at IS NOT NULL
AND deleted_at < NOW() - INTERVAL 2 YEAR;
-- Step 2: identical predicate, now destructive. ORDER BY + LIMIT keeps each
-- statement short so the transaction does not hold locks for minutes.
DELETE FROM users
WHERE deleted_at IS NOT NULL
AND deleted_at < NOW() - INTERVAL 2 YEAR
ORDER BY id
LIMIT 1000;
-- Step 3: run the DELETE in a loop from your application until this returns 0.
SELECT ROW_COUNT();When to use it
- Purge records past a retention window, in chunks, without a long-running lock.
- Delete orphaned child rows left behind by a table that had no foreign key.
- Remove a single user's data across several tables in one atomic statement.
- Reset a staging table between import runs.
More examples
Anti-join delete: rows with no surviving parent
This is the cleanup you run after discovering a table was never protected by a FOREIGN KEY. The LEFT JOIN plus IS NULL finds orphans; naming only `oi` after DELETE guarantees the orders table is read but never written. Run the same query as a SELECT COUNT(*) first — an anti-join with a slightly wrong ON clause matches everything.
-- Multi-table form. The table named between DELETE and FROM is the one emptied;
-- the others are only there to decide which rows qualify.
DELETE oi
FROM order_items AS oi
LEFT JOIN orders AS o ON o.id = oi.order_id
WHERE o.id IS NULL; -- no matching parent order exists
-- Same idea, USING spelling. Identical statement, different syntax.
DELETE FROM oi
USING order_items AS oi
LEFT JOIN orders AS o ON o.id = oi.order_id
WHERE o.id IS NULL;Deleting from several tables in one statement
Use this for a GDPR-style erasure across a handful of tables where you want one statement and one lock window. Note the join type: an INNER JOIN here means the user row is only deleted if it has rows in BOTH child tables — switch to LEFT JOIN if the children are optional, or the parent silently survives.
-- Remove a user and everything hanging off them, atomically.
-- Only needed when the schema has no ON DELETE CASCADE to do it for you.
START TRANSACTION;
DELETE u, s, p
FROM users AS u
JOIN sessions AS s ON s.user_id = u.id
JOIN profile_settings AS p ON p.user_id = u.id
WHERE u.id = 4711;
COMMIT;Emptying a table: DELETE versus TRUNCATE
TRUNCATE is the right tool for a scratch or staging table you own outright and want reset between runs. It is the wrong tool for anything audited by triggers, referenced by a FOREIGN KEY, or being cleared as one step of a larger transaction — in those cases its implicit commit will quietly break the guarantee you thought you had.
-- Removable and reversible: runs inside a transaction, fires triggers,
-- reports how many rows went, leaves the AUTO_INCREMENT counter where it was.
START TRANSACTION;
DELETE FROM import_staging;
SELECT ROW_COUNT(); -- e.g. 2400000
ROLLBACK; -- still possible
-- Not reversible: implicit commit, no ON DELETE triggers,
-- AUTO_INCREMENT reset to its start value, '0 rows affected' means 'no information'.
TRUNCATE TABLE import_staging;
Discussion