Introduction
CTEs with data modifying statements
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.
