Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Recursive Queries

Introduction
CTEs with data modifying statements
12. CTEs with DELETE statement
Summary

Instruction

OK. Finally, we will discuss how to use CTEs with DELETE. We start with an example which deletes records of customers in age less than or equal 20 who come from cities without a store:

WITH city_no_store AS (
  SELECT city FROM customer
  EXCEPT
  SELECT city FROM grocery_store
)

DELETE FROM customer
WHERE city IN (SELECT city FROM city_no_store)
AND age <= 20;

In our WITH query we select cities of customers in which no store is located. We use the EXCEPT operator. We select the cities of residence of all customers and from them subtract the city locations of all grocery stores. In this way we identify customer cities in which no store exists.

In our DELETE statement we use cities identified by the CTE to delete records from the customer table if the age of the customer is less than or equal to 20.

Exercise

Using a CTE, find the ids of customers whose age is not given in the age column. Delete these customers.

Stuck? Here's a hint!

Use a WITH query to select customer id if their age IS NULL. Use DELETE statement to delete records with IDs selected by the CTE.