DELETE
DELETE FROM table [WHERE condition] [ORDER BY …] [LIMIT n]
Deletes the rows that meet the condition. Without WHERE, it deletes the whole table (the structure stays). AUTO_INCREMENT continues from the last number.
Parameters
- WHERE – which rows to delete.
- ORDER BY … LIMIT – optional, deletes only the first n rows (for example in batches).
Examples
Examples with the customers, products and orders tables use the sample database from the SQL basics series.
DELETE FROM orders WHERE order_date < '2026-02-01'; SELECT COUNT(*) AS remaining, MIN(order_date) AS oldest FROM orders;
+-----------+------------+ | remaining | oldest | +-----------+------------+ | 4 | 2026-02-03 | +-----------+------------+
Deleting by another table – the orders of customers from Manchester:
DELETE o FROM orders o JOIN customers c ON c.id = o.customer_id WHERE c.city = 'Manchester'; SELECT COUNT(*) AS remaining FROM orders;
+-----------+ | remaining | +-----------+ | 3 | +-----------+
Things to watch out for
- DELETE without WHERE deletes everything. Try the condition as SELECT COUNT(*) with the same condition.
- Before a bulk change of data, back up the table (CREATE TABLE backup AS SELECT * FROM table, or an export) and try the change first as a SELECT with the same condition.
- Rows referenced by a foreign key cannot be deleted (or the linked rows are deleted too, according to ON DELETE). The sample database has no foreign keys.
- Do large deletions in parts (LIMIT 1000 in a loop), so that the table is not locked for long.
A detailed explanation with more examples: Inserting, updating and deleting data (the SQL basics series).
Related:
TRUNCATE TABLE
UPDATE
WHERE