🗑️DELETE

Remove rows; mind cascades and retention

DELETE removes tuples that match the predicate. Referential actions (ON DELETE CASCADE) can fan out across related tables, know the graph before running destructive SQL in shared environments. In regulated or clinical contexts, soft deletes (status flags, archive tables) often preserve an auditable trail; privacy regimes may still require hard deletes for specific subjects.

Confusing DELETE with TRUNCATE

DELETE without WHERE empties the table row by row with full trigger and FK semantics. TRUNCATE is a fast bulk reset with different permissions, trigger behavior, and rollback rules depending on dialect, never swap them casually.

Pick the tool on semantics, not speed alone

Use DELETE when you need row-level predicates, triggers, or incremental cleanup. Reserve TRUNCATE for deliberate “reset staging” operations you have runbooked and permission-gated.

SQL

Scoped delete

DELETE FROM temp_parse_errors
WHERE load_batch_id = 88102;

✓Illustrative result set

rows_deleted
430

SQL

Delete using EXISTS (correlated)

DELETE FROM staging.raw_reads s
WHERE EXISTS (
  SELECT 1 FROM qc.blocklist b WHERE b.run_id = s.run_id
);

✓Illustrative result set

rows_deleted
12

🪜Large deletes

Millions of rows in one statement can lock ranges and grow the WAL for ages.

Prefer looping deletes in batches (key ranges, LIMIT where supported, or CTID windows on PostgreSQL) so replication and autovacuum keep up. Always preview with the same WHERE in a SELECT COUNT(*) first.

Who may DELETE?

In multi-tenant systems, pair table privileges with row-level security or application-level tenancy checks, a single missing tenant_id predicate has caused cross-customer data loss more than once.

TL;DR

DELETE removes rows that match the WHERE predicate; omitting WHERE deletes the whole table (often blocked in safe clients). It is logged and triggers fire per row unless bulk optimizations apply. Prefer soft-delete columns when you need audit/recovery; use RETURNING (PostgreSQL) or output clauses (SQL Server) to confirm what was removed in one round trip.