Fantastic! So far you have done exercises based on the CTEs with data modifying statements. You may also use INSERT, UPDATE, and DELETE statements with nested CTEs. Imagine you need to raise prices of all store products by 7 percent, if the average selling price of products in the store is the lowest from among all the stores. Look at the example below.

WITH avg_prices AS (
  SELECT
    grocery_store_id,
    avg(price) AS avg_price
  FROM product
  GROUP BY grocery_store_id
),
min_avg_prices AS (
  SELECT
    MIN(avg_price) AS min_price
  FROM avg_prices
)

UPDATE product
SET price = price + price * 0.07
WHERE grocery_store_id IN (
    SELECT
      grocery_store_id
    FROM avg_prices ap
    JOIN min_avg_prices map
    ON ap.avg_price = map.min_price
  );

We construct two nested CTEs. The first uses store ID to identify and average product prices for each store. The second CTE selects the minimum average price from those returned by the first.

In the 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 IDs of grocery stores with the least average prices, we compare grocery_store_ids to the ids obtained from the subquery. The subquery compares minimal value from the second CTE and average value from first CTE, getting the grocery_store_id from the first CTE.