End of Summer - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
INSERT with SELECT
Referring to other tables
INSERT, UPDATE, and DELETE with subqueries
12. UPDATE with a subquery in WHERE or SET
Summary

Instruction

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

UPDATE product
SET price = 1
WHERE id IN (
  SELECT id
  FROM product
  WHERE product.price < 1
    AND price_timestamp < '2017-01-01'
  );

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 price_timestamp < '2017-01-01';

It will return only the products where the last price update was done before 2017 (AND price_timestamp < '2017-01-01') and the product price is less than 1 (product.price < 1).

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

UPDATE product
SET price = price + (
  SELECT AVG(price)
  FROM product
  WHERE price <= 10
)
WHERE price <= 10;

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.