DML

DELETE

Q: How do I delete duplicate rows?

Use ROW_NUMBER to rank duplicates, then delete where rank > 1: DELETE FROM t WHERE id IN (SELECT id FROM (SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rn FROM t) ranked WHERE rn > 1). Always test with a SELECT first. See Delete duplicate rows.

Q: How do I delete with a JOIN?

Postgres / DuckDB: DELETE FROM target USING source WHERE target.id = source.id. MySQL / SQL Server: DELETE target FROM target JOIN source ON .... The semantics: delete rows from target that match the join condition.

Q: Should I DELETE or use a soft-delete pattern?

For user-facing tables, soft delete: add a deleted_at TIMESTAMP column, set it instead of actually deleting, filter WHERE deleted_at IS NULL in queries. You get reversibility, audit trails, and the ability to investigate "what happened to user 42?" Hard-delete only when there's a regulatory requirement (GDPR right to erasure) or the row is genuinely transient.

Q: How do I delete in batches to avoid locking the table?

Loop with LIMIT: MySQL allows DELETE FROM t WHERE ... LIMIT 1000. Postgres needs DELETE FROM t WHERE id IN (SELECT id FROM t WHERE ... LIMIT 1000). Each iteration is a smaller transaction, releasing locks between batches. Critical for large maintenance deletes on hot tables.

Q: Will DELETE free up disk space?

Eventually, after VACUUM (Postgres) or equivalent maintenance. Immediately, no: deleted rows are marked but pages remain. TRUNCATE releases pages immediately. For "delete most of this table," consider: INSERT keep-rows into a new table, DROP the original, rename — far faster.

Remove rows from a table. The table itself stays, the indexes stay, the constraints stay. Only the matched rows leave. DELETE FROM t with no WHERE empties the table; DELETE FROM t WHERE ... removes the targeted rows. Foreign key cascades and triggers fire on every deleted row.

Syntax #

sql

-- Targeted delete.
DELETE FROM table_name WHERE condition;

-- Delete every row.
DELETE FROM table_name;

-- Delete based on another table (Postgres / DuckDB syntax).
DELETE FROM target
USING source
WHERE target.id = source.id
  AND source.flag = TRUE;

Example #

Loading SQL editor...

Common patterns #

sql

-- Delete duplicates, keep one row per group (Postgres / DuckDB / Snowflake).
DELETE FROM signups
WHERE id IN (
  SELECT id FROM (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY signed_up_at) AS rn
    FROM signups
  ) ranked
  WHERE rn > 1
);

-- Delete based on a join.
DELETE FROM orders
USING users
WHERE orders.user_id = users.id
  AND users.is_test = TRUE;

-- Soft delete instead — keep the row, mark it.
UPDATE users SET deleted_at = CURRENT_TIMESTAMP WHERE id = 42;

DELETE vs TRUNCATE vs DROP #

DELETE: row-by-row, transactional, fires triggers and FK cascades, leaves the table.
TRUNCATE: empties the table fast (typically resetting it), often non-transactional or auto-committing, skips per-row triggers, leaves the table. See TRUNCATE.
DROP TABLE: removes the table itself, including its definition, indexes, and all rows. See DROP TABLE.

Rule of thumb: DELETE for surgical row removal, TRUNCATE for "empty this whole staging table fast," DROP when the table is genuinely no longer needed.

Dialect notes #

Join syntax: Postgres / DuckDB / SQLite use DELETE FROM target USING source WHERE .... MySQL uses DELETE target FROM target JOIN source ON .... SQL Server uses DELETE target FROM target JOIN source ON ....
RETURNING: Postgres, SQLite (3.35+), DuckDB return deleted rows. SQL Server has OUTPUT DELETED.*. Useful for archive-then-delete patterns.
LIMIT on DELETE: MySQL supports DELETE FROM t WHERE ... LIMIT 1000 for batched deletes; Postgres needs a WHERE id IN (SELECT id FROM ... LIMIT 1000) wrapper.
Cascade triggers: ON DELETE CASCADE foreign keys fire on every row, so deleting 1M rows from a parent can quietly delete 100M rows from children. Audit the dependency tree before bulk deletes.
BigQuery: DELETE requires a WHERE clause (the engine refuses DELETE FROM t without one, a deliberate guardrail).

FAQ #

How do I delete duplicate rows?

Use ROW_NUMBER to rank duplicates, then delete where rank > 1: DELETE FROM t WHERE id IN (SELECT id FROM (SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rn FROM t) ranked WHERE rn > 1). Always test with a SELECT first. See Delete duplicate rows.

How do I delete with a JOIN?

Postgres / DuckDB: DELETE FROM target USING source WHERE target.id = source.id. MySQL / SQL Server: DELETE target FROM target JOIN source ON .... The semantics: delete rows from target that match the join condition.

What happens if my DELETE has no WHERE clause?

Every row is deleted. The engine cheerfully obeys. BigQuery is the exception — it requires a WHERE clause for DELETE (a deliberate guardrail). Always wrap risky deletes in a transaction; always run a SELECT COUNT(*) with the same WHERE first.

Should I DELETE or use a soft-delete pattern?

For user-facing tables, soft delete: add a deleted_at TIMESTAMP column, set it instead of actually deleting, filter WHERE deleted_at IS NULL in queries. You get reversibility, audit trails, and the ability to investigate "what happened to user 42?" Hard-delete only when there's a regulatory requirement (GDPR right to erasure) or the row is genuinely transient.

How do I delete in batches to avoid locking the table?

Loop with LIMIT: MySQL allows DELETE FROM t WHERE ... LIMIT 1000. Postgres needs DELETE FROM t WHERE id IN (SELECT id FROM t WHERE ... LIMIT 1000). Each iteration is a smaller transaction, releasing locks between batches. Critical for large maintenance deletes on hot tables.

Will DELETE free up disk space?

Eventually, after VACUUM (Postgres) or equivalent maintenance. Immediately, no: deleted rows are marked but pages remain. TRUNCATE releases pages immediately. For "delete most of this table," consider: INSERT keep-rows into a new table, DROP the original, rename — far faster.

Warning

Soft delete beats hard delete for almost any user-facing table. Add a deleted_at TIMESTAMP column, set it on "delete," and filter WHERE deleted_at IS NULL everywhere else. You get reversibility, audit trails, and the ability to investigate "wait, what happened to user 42's account?" — none of which exist after a real DELETE. Hard-delete only when there's a regulatory requirement (GDPR right to erasure) or the row is obviously transient (sessions, caches, raw event staging).