SQL Reference ยท Statements and Clauses
SQL DELETE statement
Reference for SQL DELETE: remove rows with a WHERE condition, delete using subqueries and joins, and know how foreign keys and transactions affect deletes.
DELETE FROM removes rows from a table. A WHERE clause selects the rows to remove; leave it out and every row is deleted, although the table itself, its columns and its indexes stay.
Deletion is permanent once committed. Test the condition with a SELECT first, and do risky deletes inside a transaction.
DELETE FROM orders WHERE status = 'cancelled';
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
DELETE FROM table_name [WHERE condition];
| Part | Description |
|---|---|
table_name |
The table to remove rows from. |
WHERE |
Rows for which the condition is true are deleted. Rows where it is false or NULL are kept. |
Result: the number of rows deleted. Zero is not an error. Deleting a row that other rows reference fails, or cascades, depending on the foreign key's ON DELETE action.
Dialect differences
| Dialect | Notes |
|---|---|
| SQLite | DELETE FROM t WHERE ... with subqueries. RETURNING supported (3.35+). LIMIT works only if SQLite was compiled with the option. Plain DELETE FROM t is optimized to empty the table quickly. |
| PostgreSQL | DELETE FROM t USING other WHERE ... to delete by join, plus RETURNING. |
| MySQL | DELETE t FROM t JOIN other ON ... WHERE ...; ORDER BY and LIMIT allowed on single-table deletes. |
| SQL Server | DELETE t FROM t JOIN other ON ... WHERE ...; DELETE TOP (n); OUTPUT deleted.*. |
| Oracle | DELETE FROM t WHERE ... with subqueries; RETURNING ... INTO. |
-- PostgreSQL DELETE FROM order_items AS oi USING orders AS o WHERE o.id = oi.order_id AND o.status = 'cancelled'; -- MySQL / SQL Server DELETE oi FROM order_items AS oi JOIN orders AS o ON o.id = oi.order_id WHERE o.status = 'cancelled';
Usage notes
- No
WHEREmeans all rows. Run the condition throughSELECTfirst. - Foreign keys: deleting a parent that still has children fails, unless the key says
ON DELETE CASCADEorSET NULL. In SQLite this is enforced only afterPRAGMA foreign_keys = ON. SeeON DELETEactions. NOT INwithNULL:NOT IN (subquery)deletes nothing if the subquery returns anyNULL. PreferNOT EXISTS.- Deleting everything:
TRUNCATE TABLEis faster in databases that have it, but it is DDL and usually cannot be filtered. - Soft deletes: many applications set a
deleted_atcolumn withUPDATEinstead, to keep history and allow undo. - Space is not always returned: SQLite and others reuse freed pages; run
VACUUMto shrink the file. - Triggers fire per deleted row where defined.
- Huge deletes hold locks and grow logs; delete in batches.
Related
TRUNCATE TABLE,INSERT INTO,UPDATE,RETURNING,EXISTS- Lessons: DELETE, Foreign keys, Transactions