Open navigation menu

DELETE with Subquery and RETURNING

Uses DELETE with subquery conditions to remove specific rows and RETURNING to capture deleted data for audit logging and referential cleanup.

SqlData Integrity

Code

snippet
BEGIN;

-- Archive deleted records before removing
WITH deleted_orders AS (
    DELETE FROM orders
    WHERE status = 'cancelled'
      AND order_date < CURRENT_DATE - INTERVAL '90 days'
      AND customer_id IN (
          SELECT customer_id
          FROM customers
          WHERE is_active = false
      )
    RETURNING
        order_id,
        customer_id,
        order_date,
        total_amount,
        status
)
INSERT INTO archived_orders (order_id, customer_id, order_date, total_amount, status, archived_at)
SELECT
    order_id,
    customer_id,
    order_date,
    total_amount,
    status,
    CURRENT_TIMESTAMP
FROM deleted_orders;

-- Also clean up related order items
DELETE FROM order_items
WHERE order_id NOT IN (SELECT order_id FROM orders);

COMMIT;

Related Examples