Summer Deals - 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 JOIN
INSERT, UPDATE, and DELETE with subqueries
13. 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 ProductId FROM Purchase) 
AND YEAR(DeliveredDateTime) < YEAR(GETDATE()) - 3;

The subquery allows us to select the ID of all products that have appeared in an order (SELECT ProductId FROM Order). 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:

YEAR(DeliveredDateTime) < YEAR(GETDATE()) - 3
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

YEAR(OrderDate) >= 2017

in WHERE.