Kickstart 2020 with new opportunities! - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
INSERT with SELECT
Referring to other tables
INSERT, UPDATE, and DELETE with subqueries
11. DELETE with a subquery in WHERE
Summary

Instruction

Good job! The last way to refer to another table in an INSERT, UPDATE, or DELETE statement is to use a subquery. Here is a DELETE statement that uses a subquery in the WHERE clause:

DELETE FROM product
WHERE id NOT IN (SELECT product_id FROM purchase)
  AND delivered_timestamp < CURRENT_TIMESTAMP - INTERVAL '3 years';

The subquery allows us to select the ID of all products that have appeared in an order (SELECT product_id FROM purchase). We're looking for products that have not been ordered (i.e., where the product ID is not in the list returned by the subquery) – hence the NOT IN operator. These are products that were not bought by anybody.

There's an additional condition in the WHERE clause:

delivered_timestamp < CURRENT_TIMESTAMP - INTERVAL '3 years';

This checks if each product's delivery date is more than three years old. Products not bought within the last three years are withdrawn.

Exercise

The manager has decided to remove the records for all employees who haven't made a sale since 2017. Help him, using a subquery in the WHERE clause.

Stuck? Here's a hint!

Use this expression in the WHERE clause:

order_date >= '2017-01-01'