Good job! The last way to refer to another table in an
DELETE statement is to use a subquery. Here is a
DELETE statement that uses a subquery in the
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
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.