SQL DELETE Statement

DELETE removes rows from a table. Unlike DROP TABLE (which removes the entire table structure) or TRUNCATE (which wipes all rows without logging individual deletions), DELETE lets you remove specific rows based on conditions — and it’s transactional, meaning you can roll it back if something goes wrong.

Basic Syntax

DELETE FROM table_name
WHERE condition;

Without a WHERE clause, DELETE removes every row in the table:

-- Removes ALL rows in the table (but keeps the table structure)
DELETE FROM temp_staging;

Always include a WHERE clause unless wiping the entire table is intentional.

Common DELETE Patterns

Delete a specific row by primary key:

DELETE FROM customers WHERE customer_id = 1042;

Delete rows matching a condition:

DELETE FROM sessions WHERE expires_at < NOW();
DELETE FROM audit_log WHERE created_at < NOW() - INTERVAL '90 days';

Delete rows matching multiple conditions:

DELETE FROM orders
WHERE status = 'cancelled'
  AND created_at < '2024-01-01';

Preview before deleting (run as SELECT first):

-- Always verify your WHERE clause before running DELETE
SELECT * FROM orders
WHERE status = 'cancelled'
  AND created_at < '2024-01-01';

-- Then run the delete
DELETE FROM orders
WHERE status = 'cancelled'
  AND created_at < '2024-01-01';

Deleting with a Subquery

When the condition for deletion requires data from another table, use a subquery:

-- Delete orders for customers who have been deactivated
DELETE FROM orders
WHERE customer_id IN (
    SELECT customer_id
    FROM customers
    WHERE is_active = FALSE
);

DELETE with JOIN (MySQL and SQL Server)

PostgreSQL doesn’t support DELETE ... JOIN directly — use a subquery or CTE instead. MySQL and SQL Server do:

-- MySQL: delete orders for inactive customers using JOIN
DELETE o
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.is_active = FALSE;

-- SQL Server
DELETE o
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.is_active = 0;

PostgreSQL equivalent using USING:

-- PostgreSQL: USING clause for join-based delete
DELETE FROM orders o
USING customers c
WHERE o.customer_id = c.customer_id
  AND c.is_active = FALSE;

DELETE with CTE (PostgreSQL)

PostgreSQL supports DELETE inside a CTE, which enables complex multi-step delete logic:

-- Delete and return deleted rows
WITH deleted AS (
    DELETE FROM sessions
    WHERE expires_at < NOW()
    RETURNING session_id, user_id, expires_at
)
SELECT COUNT(*) AS deleted_count FROM deleted;

The RETURNING clause lets you capture what was deleted — useful for auditing or cascading operations.

-- Move rows to archive before deleting
WITH archived AS (
    INSERT INTO orders_archive
    SELECT * FROM orders WHERE status = 'cancelled' AND created_at < '2024-01-01'
    RETURNING order_id
)
DELETE FROM orders
WHERE order_id IN (SELECT order_id FROM archived);

Soft Delete Pattern

Many production systems avoid permanently deleting rows — instead, they mark rows as deleted using a flag. This preserves history, enables recovery, and simplifies audit trails.

-- Add a deleted_at column instead of deleting
ALTER TABLE customers ADD COLUMN deleted_at TIMESTAMPTZ;

-- "Delete" by setting the flag
UPDATE customers
SET deleted_at = NOW()
WHERE customer_id = 1042;

-- Query active customers (exclude soft-deleted)
SELECT * FROM customers WHERE deleted_at IS NULL;

This approach trades simplicity for safety. The main cost is that queries must always filter on deleted_at IS NULL, and tables grow indefinitely unless archived periodically.

DELETE vs TRUNCATE

Both remove rows, but they work differently:

DELETE vs TRUNCATE comparison

  Feature            | DELETE              | TRUNCATE
  ─────────────────────────────────────────────────────
  Removes rows       | Yes                 | Yes
  Keeps structure    | Yes                 | Yes
  WHERE clause       | Yes                 | No (all rows)
  Transactional      | Yes (rollback OK)   | Yes in PostgreSQL; varies elsewhere
  Triggers fire      | Yes                 | No (most databases)
  Resets sequences   | No                  | Yes (with RESTART IDENTITY)
  Speed on large tables | Slow (logs rows) | Fast (operates on pages)
  Affected row count | Available           | Not available (MySQL: returns 0)

Use TRUNCATE when you need to wipe an entire table fast (like clearing a staging table before a reload). Use DELETE when you need conditions, triggers to fire, or the ability to roll back individual row deletions.

-- Truncate with identity reset (PostgreSQL)
TRUNCATE TABLE staging_orders RESTART IDENTITY;

-- Truncate with cascade to child tables
TRUNCATE TABLE customers CASCADE;  -- also truncates orders, sessions, etc.

Foreign Key Constraints and DELETE

If a foreign key constraint has ON DELETE RESTRICT (the default), deleting a parent row that is referenced by child rows will fail:

-- This fails if any orders reference customer_id = 1042
DELETE FROM customers WHERE customer_id = 1042;
-- ERROR: update or delete on table "customers" violates foreign key constraint

Options:

Delete child rows first, then delete the parent
Use ON DELETE CASCADE in the foreign key definition (automatically deletes children)
Use ON DELETE SET NULL to clear the reference in children

-- Delete in the right order
DELETE FROM orders WHERE customer_id = 1042;
DELETE FROM addresses WHERE customer_id = 1042;
DELETE FROM customers WHERE customer_id = 1042;

Wrapping this in a transaction ensures all deletions succeed or none do:

BEGIN;
    DELETE FROM orders WHERE customer_id = 1042;
    DELETE FROM addresses WHERE customer_id = 1042;
    DELETE FROM customers WHERE customer_id = 1042;
COMMIT;

Batched Deletes for Large Datasets

Deleting millions of rows at once can lock the table, fill up transaction logs, and impact production performance. Batch the deletion:

-- Delete 10,000 rows at a time until none remain
DO $$
DECLARE deleted_count INTEGER;
BEGIN
    LOOP
        DELETE FROM audit_log
        WHERE id IN (
            SELECT id FROM audit_log
            WHERE created_at < NOW() - INTERVAL '1 year'
            LIMIT 10000
        );
        GET DIAGNOSTICS deleted_count = ROW_COUNT;
        EXIT WHEN deleted_count = 0;
        PERFORM pg_sleep(0.1);  -- brief pause between batches
    END LOOP;
END $$;

This pattern keeps locks short, allows other transactions to interleave, and prevents transaction log exhaustion.