IntermediateCTEs & Recursive

CTE in DELETE: remove old orders

The query

SQL

WITH old_cancelled AS (
  SELECT order_id
  FROM orders
  WHERE status = 'cancelled'
    AND order_date < NOW() - INTERVAL '2 years'
)
DELETE FROM orders
WHERE order_id IN (SELECT order_id FROM old_cancelled);

Tested against PostgreSQL 16

Note

“CTEs in DELETE statements make the logic explicit and reviewable before execution.

Tables referenced

orders6 cols