UPDATE … FROM & aggregated changes

Update rows from other rows

Lesson 17 of 20 · 11 min · Module 5: Change data safely

Pending orders should reserve stock so it is not sold twice. The amount to subtract comes from another table, summed per product. UPDATE … FROM applies such calculated changes in one statement.

Start this lesson →

Free. No installation: you write and run the code in your browser, and it is checked automatically.

By the end, you’ll be able to…

Update one table using another
Aggregate the source before updating
Check the effect of a write

The idea

UPDATE products SET stock = stock - r.qty FROM (subquery) AS r WHERE products.id = r.product_id joins the target table to a result set and updates matching rows. Summarise the source per target row first, so each product is updated exactly once.

UPDATE products SET stock = stock - 1
WHERE id IN (SELECT product_id FROM order_items WHERE order_id = 11);
SELECT id, stock FROM products WHERE id IN (2, 7);

01Join, then set

UPDATE … FROM works like a join: every product row that matches a row of reserved is updated with values from it. Products without a match are left alone.

02One source row per target

If reserved had two rows for the same product, only one would be applied. Grouping by product_id first makes the subtraction complete and predictable.

03Preview with SELECT

Before running an UPDATE on real data, run the same FROM and WHERE as a SELECT to see which rows would change and by how much.

SELECT oi.product_id, SUM(oi.quantity)
FROM order_items AS oi JOIN orders AS o ON o.id = oi.order_id
WHERE o.status = 'pending' GROUP BY oi.product_id;

Output

2 | 1
4 | 1
7 | 1

Your turn

Reserve stock for every order with status pending: subtract the total quantity of each product in those orders from its stock, using one UPDATE … FROM. Then select id, name, stock for products 2, 4 and 7, sorted by id.

What the checks look for

Reserves one unit each of Travel Mug, Dark Coffee and Tea Pot
Leaves products without pending orders unchanged

Try it in the editor →

Take this with you

Summarise the change, preview it, then apply it in one statement.

Go a little further

Wrap the reservation and changing the orders’ status to "paid" in one transaction with BEGIN and COMMIT.