DELETE

DELETE removes rows from a table. Like UPDATE, it can target a single row, a filtered subset of rows, or — if you are not careful — every row in the table at once.

Basic syntax

DELETE syntax

SQL

DELETE FROM table_name
WHERE condition;

For example, to remove a single customer record:

Deleting one row

SQL

DELETE FROM customers
WHERE id = 501;

Deleting many rows with a condition

Deleting rows matching a condition

SQL

DELETE FROM sessions
WHERE last_active < NOW() - INTERVAL '90 days';

Any valid WHERE condition works here, including ones that reference subqueries:

Deleting based on a subquery

SQL

DELETE FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE is_deleted = TRUE
);

Forgetting WHERE deletes every row

Omitting WHERE removes every row in the table, with no confirmation:

Dangerous — no WHERE clause

SQL

-- This deletes every single row in the table
DELETE FROM customers;

The table still exists

Unlike DROP TABLE, an unfiltered DELETE leaves the (now empty) table structure in place — but every row of data is gone. If you genuinely want to empty an entire table, TRUNCATE is usually the faster, more appropriate tool.

DELETE vs TRUNCATE

These two are easy to mix up. The key difference is that DELETE can be filtered with WHERE and logs each row removal, while TRUNCATE always removes everything at once and skips per-row logging:

	DELETE FROM table WHERE ...	TRUNCATE TABLE table
Can target specific rows	Yes	No — always all rows
Relative speed on large tables	Slower	Much faster
Fires row-level triggers	Yes	Usually no

See the dedicated DROP & TRUNCATE page for the full three-way comparison including DROP TABLE.

Tip

Just like with UPDATE, run the equivalent SELECT with the same WHERE clause first to see exactly which rows will be removed before committing to the DELETE:

Verify first, then delete

SQL

-- Step 1: see exactly what would be deleted
SELECT * FROM sessions
WHERE last_active < NOW() - INTERVAL '90 days';

-- Step 2: once the results look correct, delete them
DELETE FROM sessions
WHERE last_active < NOW() - INTERVAL '90 days';

Running DELETE inside an explicit transaction lets you inspect the affected row count and ROLLBACK if it doesn't match your expectations
Foreign key constraints may block a DELETE if other rows still reference the row you're removing — you'll typically need to delete the dependent rows first, or configure a cascading delete
For very large tables, deleting in smaller batches (e.g. 10,000 rows at a time) can avoid long lock times and excessive transaction log growth