UPDATE pitfalls

Learn the danger of UPDATE without WHERE.

Update with subquery

You can use a subquery inside UPDATE to write a value from another table.

UPDATE products
SET price = (SELECT max_price FROM price_list WHERE pid = products.id);

This query takes each product's price from the price_list table.

In products, price = (SELECT max_price is updated.

The subquery must return a single value; otherwise it errors. A correlated subquery runs per row.

Set price to 10 for id=1 in products.

Show example solution

UPDATE products
SET price = 10
WHERE id = 1

Subquery

UPDATE products
SET price = (____ max_price FROM price_list WHERE pid = products.id);

Show answer

UPDATE products
SET price = (SELECT max_price FROM price_list WHERE pid = products.id);