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
SELECT city FROM grocery_store
DELETE FROM customer
WHERE city IN (SELECT city FROM city_no_store)
AND age <= 20;
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.
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.