It's Autumn! Prices fall like leaves! - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Using CTEs with data-modifying statements
8. CTEs with INSERT statement 2
Summary

Instruction

Very good. Look at another example of a CTE with an INSERT statement.

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

INSERT INTO grocery_avg_prices 
SELECT
  *
FROM avg_prices
WHERE grocery_store_id != 2;

In this case, we created a CTE to retrieve the average product price of products for each grocery store. We used the AVG aggregate function to compute this average. Then we used GROUP BY to group the records according to grocery_store_id.

In the outer query, we inserted rows into table grocery_avg_prices, but only for grocery stores with an ID other than 2.

Exercise

We have created a new table called product_max_price. Let's insert product names and their respective highest prices into this table.

In the CTE, select the name and maximum price for every product.

In the outer statement, insert product names and max prices into the table product_max_price, but only for 'Bread' and 'Milk' products.

Stuck? Here's a hint!

In the CTE, select the name and maximum price for every product in a CTE. In the outer query, insert the name and price data for 'Bread' and 'Milk' products into the table product_max_price.