On January 21th at 14:15 UTC , progression through exercises will be unavailable for 10 minutes due to a planned maintenance break.
Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Referring to other tables
9. Update rows by referring to another table
INSERT, UPDATE, and DELETE with subqueries


Very nice! You also can use references to other tables in an UPDATE statement. If you need to update a row using data from another table, you can write a statement like the one below:

UPDATE purchase
  total_price = product.price * purchase.quantity
FROM product
WHERE purchase.product_id = product.id;

This statement will update product amounts in the purchase table using price details from the product table. The additional table (product) is given after FROM, which is listed after SET. The WHERE condition comes immediately after FROM; in this case, it is joining the two tables by ID (purchase.product_id = product.id). Notice that by choosing a new product price, we can change the amount in the order.


The manager wants to give a raise to each employee who sold over $1,000 in one order (based on the total_price column in the purchase table). Use UPDATE ... FROM to add $100 to the salary of each employee who meets the qualification.

Stuck? Here's a hint!

Use in SET:

salary = employee.salary + 100

Use in WHERE:

purchase.employee_id = employee.id
  AND purchase.total_price > 1000