Fantastic! So far, you have done exercises based on using a single CTE with data-modifying statements. You may also use
DELETE statements with nested CTEs.
Imagine you need to raise the prices of all store products by 7 percent, but only if the average selling price of all products in the store is the lowest of all the stores. (Note: Usually, this will apply to just one store, but it's possible that two or more stores will have identical prices and will be the lowest.) Look at the example below.
WITH avg_prices AS (
avg(price) AS avg_price
GROUP BY grocery_store_id
min_avg_prices AS (
MIN(avg_price) AS min_price
SET price = price + price * 0.07
WHERE grocery_store_id IN (
FROM avg_prices ap
JOIN min_avg_prices map
ON ap.avg_price = map.min_price
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 the averages returned by the first CTE.
UPDATE statement, we calculate and apply a new product price, increased by 7 percent, to the grocery store(s) whose IDs are identified with the lowest average product price.
To obtain the IDs of grocery store(s) with the lowest average prices, we compare
grocery_store_ids to the IDs obtained from the subquery. The subquery compares the smallest value from the second CTE and the average value return by the first CTE, getting the
grocery_store_id from the first CTE.