UPDATE ... FROM and DELETE ... USING

Change rows based on other tables

PostgreSQL lets UPDATE join other tables with FROM and DELETE with USING, so you can change rows based on related data without correlated subqueries. RETURNING shows exactly which rows changed, which is useful for audits and for feeding results back to applications. Make sure the join matches at most one source row per target row, otherwise the update value is unpredictable.

A raise for Sales and removing items of cancelled orders, run

I ran this with psql on PostgreSQL 16.2 against the course sample data (shown in the first section). RETURNING lists the three Sales employees with their new salaries and the deleted item of cancelled order 107.

-- Give everyone in Sales a 10% raise, using a join in UPDATE
UPDATE employees e SET salary = round(e.salary * 1.10)
FROM departments d
WHERE d.id = e.dept_id AND d.name = 'Sales'
RETURNING e.name, e.salary;

-- Delete order items belonging to cancelled orders
DELETE FROM order_items i USING orders o
WHERE o.id = i.order_id AND o.status = 'cancelled'
RETURNING i.order_id, i.product;

Output:

 name | salary 
------+--------
 Sara | 165000
 Ken  |  99000
 Li   | 104500
(3 rows)

 order_id | product 
----------+---------
      107 | pen
(1 row)

Write the SELECT first

Turn the UPDATE into a SELECT with the same FROM and WHERE to check the affected rows before running it.

Quick check: What does RETURNING do on an UPDATE?

• Rolls back the update
• Returns the modified rows, like a SELECT
• Returns only a count
• Locks the whole table