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 in MS SQL Server

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

Instruction

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
  EXCEPT
  SELECT city FROM GroceryStore
)

DELETE FROM Customer
WHERE City IN (SELECT City FROM CityNoStore)
AND Age <= 20;

In our 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.

In our 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.

Exercise

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 all customer Ids where Age IS NULL. Use a DELETE statement to delete records with IDs selected by the CTE.