Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
CTEs with data modifying statements
7. CTEs with INSERT statement 1
Summary

Instruction

OK. Now let's begin to tackle CTEs with INSERT. Look at the code below.

WITH plus30 AS (
  SELECT
    id,
    first_name,
    last_name,
    city
  FROM customer
  WHERE age >= 30 
)

INSERT INTO customer_30plus 
SELECT
  *
FROM plus30;

In the WITH query we select the id, first_name, last_name, and city of customers who are over 30 years old.

In the outer query, the data returned by the CTE plus30 are inserted into table customer_30plus. Note that the outer INSERT statement can refer to the CTE plus30.

Exercise

We created another table called groceries_customers. Fill it with the following: id, name, and city of grocery store, only if any customers live in that city.

Stuck? Here's a hint!

In the CTE query, select data from grocery_store table. In the outer query, select all data from the CTE, and insert them into our new groceries_customers table.