SQL DELETE: Remove Rows From a Table (and DELETE vs TRUNCATE)

DELETE removes rows from a table. Like UPDATE, it is short to write and permanent once committed, so the same safety habits apply.

The example table#

orders

Syntax#

DELETE FROM table_name
WHERE condition;

There are no column names: DELETE always removes whole rows. To clear just one value, use UPDATE ... SET column = NULL.

Delete one row#

DELETE FROM orders
WHERE order_id = 104;

SELECT * FROM orders;

order_id | customer_id | amount | order_date
---------+-------------+--------+-----------
101      | 1           | 250    | 2026-01-05
102      | 1           | 120    | 2026-02-11
103      | 2           | 560    | 2026-02-20

Delete many rows#

Every row matching the condition is removed:

DELETE FROM orders
WHERE amount < 200;

SELECT order_id, amount FROM orders;

order_id | amount
---------+-------
101      | 250
103      | 560

Orders 102 (120) and 104 (90) were both deleted.

Delete using another table#

Order 104 points to customer 5, who does not exist. Remove all such "orphan" orders with a subquery:

DELETE FROM orders
WHERE customer_id NOT IN (SELECT customer_id FROM customers);

order_id | customer_id
---------+------------
101      | 1
102      | 1
103      | 2

(If customers.customer_id could ever be NULL, use NOT EXISTS instead of NOT IN; see EXISTS.)

DELETE without WHERE#

DELETE FROM orders;

This removes every row. The table still exists, but it is empty. As with UPDATE, there is no undo unless you are inside a transaction.

DELETE vs TRUNCATE vs DROP#

SQLite has no TRUNCATE; DELETE FROM t without WHERE is optimised to do the same job.

Foreign keys and deletes#

If other tables point to a row through a foreign key, the database may refuse to delete it, because that would leave orphans. You can define what should happen instead: ON DELETE CASCADE deletes the related rows too, and ON DELETE SET NULL clears the reference.

Soft deletes#

Many applications never actually DELETE important records. Instead they mark them:

UPDATE customers
SET deleted_at = '2026-10-23'
WHERE customer_id = 4;

Queries then filter with WHERE deleted_at IS NULL. This keeps history and makes "undo" easy, at the cost of remembering that filter everywhere.

Common mistakes#

Practice#

SELECT order_id, order_date
FROM orders
WHERE order_date < '2026-02-01';

order_id | order_date
---------+-----------
101      | 2026-01-05

DELETE FROM orders
WHERE order_date < '2026-02-01';

Key takeaway#