Open navigation menu

UPDATE with FROM and RETURNING

Performs UPDATE with a FROM clause to join additional tables and uses RETURNING to retrieve modified rows for verification and auditing.

SqlData Integrity

Code

snippet
UPDATE products p
SET
    unit_price = p.unit_price * (1 + adj.price_adjustment),
    updated_at = CURRENT_TIMESTAMP,
    updated_by = current_user
FROM price_adjustments adj
WHERE p.product_id = adj.product_id
  AND adj.effective_date <= CURRENT_DATE
  AND adj.is_active = true
RETURNING
    p.product_id,
    p.product_name,
    p.unit_price AS new_price,
    (p.unit_price / (1 + adj.price_adjustment)) AS old_price,
    adj.price_adjustment AS adjustment;

Related Examples