QuerydrillDELETE
DELETE FROM table WHERE condition removes every row that matches: DELETE FROM orders WHERE status = 'cancelled' AND order_date < '2024-01-01'. Without WHERE it empties the table. Rows referenced by a foreign key cannot be deleted unless the constraint allows it (ON DELETE CASCADE).
Syntax
DELETE FROM table WHERE condition;Examples
DELETE FROM orders WHERE id = 42;Result: 1 row deleted
DELETE FROM orders WHERE status = 'cancelled' AND order_date < '2024-01-01';Result: Old cancelled orders removed
DELETE FROM customers WHERE id NOT IN (SELECT MIN(id) FROM customers GROUP BY email);Result: Deletes duplicate customers, keeping the lowest id per email
Common mistakes
- DELETE FROM table with no WHERE removes all rows; verify the condition with SELECT first and use a transaction.
- FOREIGN KEY constraint failed when deleting a parent row that still has children; delete the children first or define ON DELETE CASCADE.
- Writing DELETE * FROM table (as in some tutorials) is a syntax error; DELETE takes no column list.
Practice it
The drills on Querydrill check your answer on live data, free, no account needed.
Start practicingQuestions
What is the difference between DELETE and TRUNCATE?
DELETE removes rows one by one, can have a WHERE and fires triggers; TRUNCATE (PostgreSQL) empties the whole table instantly and can reset identity counters with RESTART IDENTITY. SQLite has no TRUNCATE; DELETE FROM table without WHERE uses a fast path.
How do I delete rows that exist in another table?
DELETE FROM a WHERE id IN (SELECT a_id FROM b) or WHERE EXISTS (...). PostgreSQL also supports DELETE FROM a USING b WHERE b.a_id = a.id.
Related
- UPDATEUPDATE table SET column = value WHERE condition changes matching rows. Several columns, compute from the current value, update from another table, stay safe.
- INSERT INTOINSERT INTO adds rows to a table. Syntax with a column list, inserting multiple rows in one statement, INSERT ... SELECT to copy data, RETURNING the new id.
- EXISTSEXISTS is true when the subquery returns at least one row. Use it for has-any checks and NOT EXISTS for rows with no match, the safe alternative to NOT IN.
- WHEREUse WHERE to keep only the rows that match a condition: equals, greater than, text matches, NULL checks, and combinations with AND, OR and NOT.