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:

UPDATE Product
SET Price = Price + (
  SELECT AVG(Price)
  FROM Product
  WHERE Price <= 10
)
WHERE Price <= 10