Kickstart 2020 with new opportunities! - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
INSERT with SELECT
Referring to other tables
9. Update rows by referring to another table
INSERT, UPDATE, and DELETE with subqueries
Summary

Instruction

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
SET
  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.

Exercise

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