Introduction
CTEs with data modifying statements
10. CTEs with UPDATE statement
Summary

Instruction

Excellent! Now we'll discuss how to use the UPDATE statement with CTEs. Look at this example:

WITH AveragePrice AS (
  SELECT
    AVG(Price) AS AvgPrice
  FROM Product
)

UPDATE Product
SET Price = Price - (SELECT AvgPrice FROM AveragePrice) / 100
WHERE GroceryId = 1;

In the WITH, clause we selected the average price for all products in all stores. This information is then used in our UPDATE statement. The Green Shop grocery, with Id = 1, is discounting its product prices by one percent of the average price for all products in all stores. Lastly, the prices of all products in the grocery store with Id = 1 are changed to the new lower prices.

Exercise

Change the price of all products with a Price between 0.50 and 1.00, raising them by the price of the cheapest product. Use a CTE to select the price of that product.

Stuck? Here's a hint!

Use the MIN() function.