Finally, we will discuss how to use CTEs with
DELETE. We'll start with an example which deletes the records of customers aged 20 or less and who come from cities without a store:
WITH city_no_store AS (
SELECT city FROM customer
SELECT city FROM grocery
DELETE FROM customer
WHERE city IN (SELECT city FROM city_no_store)
AND age <= 20;
WITH query, we use the
EXCEPT operator to select customers' citieswhere no store is located. We select the cities of residence of all customers and from them we subtract the city locations of all grocery stores. In this way, we identify cities that have customers but no stores.
DELETE statement, we use the cities identified by the CTE to delete records from the customer table if the age of the customer is 20 or less.