Introduction
INSERT with SELECT
Referring to other tables
INSERT, UPDATE and DELETE with JOIN
INSERT, UPDATE, and DELETE with subqueries
14. UPDATE with a subquery in WHERE or SET
Summary

Instruction

Nice! You can also use a subquery in an UPDATE operation, as you see in the query below:

UPDATE Product 
SET Price = 1 
WHERE Id IN (
  SELECT Id 
  FROM Product 
  WHERE Product.Price < 1 
  AND YEAR(PriceDateTime) < 2017);

This updates certain products by changing their price to 1. In WHERE, we use the condition that the product's ID has to be in the list of the product IDs returned by the subquery. Have another look at the subquery:

SELECT Id 
FROM Product 
WHERE Product.Price < 1 
AND YEAR(PriceDateTime) < 2017

It will return only the products where the last price update was done before 2017 (YEAR(PriceDateTime) < 2017) and the product price is less than 1 (Product.Price < 1).

Note that you can also place a subquery in the SET clause of UPDATE.

Exercise

Use a subquery to update order amounts. You'll be increasing the price of each product by 20% and multiplying that figure by the product's quantity in that order.

Stuck? Here's a hint!

Use a subquery in SET.