✏️ Modifying DatabeginnerCommonly used

How to delete rows in SQL

Remove one or more rows from a table.

Delete specific rowsAll databases

DELETE FROM users
WHERE id = 42;

Deletes only the row where id = 42.

Always include WHERE unless you want to delete everything.

Delete with multiple conditionsAll databases

DELETE FROM sessions
WHERE user_id = 42
  AND created_at < NOW() - INTERVAL '30 days';

Deletes only rows matching all conditions.

Delete all rows (keep table)All databases

-- Slower, recoverable, fires triggers:
DELETE FROM logs;

-- Faster, not easily recoverable, resets sequences:
TRUNCATE TABLE logs;

DELETE removes all rows but is slow and logs each deletion. TRUNCATE is faster but less safe.

When to use: TRUNCATE for large tables in maintenance. DELETE for careful, logged removal.

Delete and return deleted rows (PostgreSQL)PostgreSQL

DELETE FROM users
WHERE active = false
RETURNING id, name, email;

Returns the rows that were deleted.

When to use: Audit logging, archiving deleted records.

Delete with subqueryAll databases

DELETE FROM orders
WHERE user_id IN (
  SELECT id FROM users
  WHERE active = false
);

Deletes rows based on a condition from another table.

Common Mistakes