Finally, we will discuss how to use CTEs with
DELETE. We start with an example which deletes records for customers whose age is less than or equal to 20 and who come from cities without a store:
WITH CityNoStore AS (
SELECT city FROM Customer
SELECT city FROM GroceryStore
DELETE FROM Customer
WHERE City IN (SELECT City FROM CityNoStore)
AND Age <= 20;
WITH query, we selected the cities where no store is located. We used the
EXCEPT operator. We selected the cities of residence for all customers, and from them we subtracted the city locations of all grocery stores. In this way, we identify the customer cities where no store exists.
DELETE statement, we used cities identified by the CTE to delete records from the
Customer table, but only if the age of the customer is less than or equal to 20.