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 PostgreSQL

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

Instruction

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

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

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

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

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 a customer id if the associated age IS NULL. Use the DELETE statement to remove records with the IDs selected by the CTE.