Fantastic! So far, you have done exercises based on CTEs with data modifying statements. You may also use
DELETE statements with nested CTEs. Imagine that you need to raise prices of all store products by 7 percent, but only when the products' average selling price is the lowest of all the stores. Look at the example below.
WITH AvgPrices AS (
AVG(Price) AS AvgPrice
GROUP BY GroceryStoreId
MinAvgPrices AS (
MIN(AvgPrice) AS MinPrice
SET Price = Price + Price * 0.07
WHERE GroceryStoreId IN (
FROM AvgPrices AP
INNER JOIN MinAvgPrices MAP
ON AP.AvgPrice = MAP.MinPrice
We constructed two nested CTEs. The first uses store IDs to identify and average product prices for each store. The second CTE selects the minimum average price from those returned by the first CTE.
UPDATE statement, we calculate and apply a new product price, increased by 7 percent, to grocery stores whose IDs are identified with the lowest average product price.
To obtain the IDs of grocery stores with the lowest average prices, we compare
GroceryStoreIds to the IDs obtained from the subquery. The subquery compares minimal values from the second CTE and average values from the first CTE, getting the
GroceryStoreId from the first CTE.