Introduction
INSERT with SELECT
Referring to other tables
INSERT, UPDATE and DELETE with JOIN
12. Removing rows using JOIN
INSERT, UPDATE, and DELETE with subqueries
Summary

Instruction

And now, the last operation with JOIN: DELETE.

The query below removes orders where the product's delivery date is 2017 or earlier:

DELETE Purchase 
FROM Purchase 
JOIN Product ON Purchase.ProductId = Product.Id 
WHERE YEAR(Product.DeliveredDateTime) <= 2017;

In a simple DELETE query, we place the name of the table from which we'll remove records after the DELETE keyword. If we're joining another table, we put another FROM clause with the same table name (again) and JOIN, followed by the name of the joining table. Next, we have ON and the join conditions (Purchase.ProductId = Product.Id). Notice that we use the same table after both DELETE and FROM.

Another way you can write this query is:

DELETE FROM Purchase 
FROM Purchase 
JOIN Product ON Purchase.ProductId = Product.Id 
WHERE YEAR(Product.DeliveredDateTime) <= 2017;

Exercise

Remove from the Purchase table all details of N'Paper bag' orders.

Stuck? Here's a hint!

Use

WHERE Product.Name = N'Paper bag'