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
