Fantastic! INSERT is not the only statement that allows you to refer to another table. The DELETE or UPDATE statements do as well.
Imagine that an employee forgot to enter prices for some products that just arrived. However, some of these products have already been included in orders, even though they have not been priced. We can't give things away, so we need to delete these products from those orders.
For such a problem, we use the DELETE FROM ... FROM structure. Here's a statement that will do the trick:
DELETE FROM Purchase FROM Product WHERE Product.Id = Purchase.ProductId AND Product.Price IS NULL;
After DELETE FROM, we have the table from which the rows will be deleted. Next is the second FROM clause and a second table, the one which we will use to determine which rows to delete. We can do this thanks to a WHERE that joins these tables with the condition Product.Id = Purchase.ProductId and specifies the condition Product.Price IS NULL. You could use other conditions here as well.
There is a shorter version of this syntax that omits the first FROM:
DELETE Purchase FROM Product WHERE Product.Id = Purchase.ProductId AND Product.Price IS NULL;
A customer, Mr. Smith, recently placed an order. However, he has decided he does not need some of the products he purchased and would like to remove them from the order. Help the sales employee remove products with Id = 3 that were ordered by Mr. Smith from the Purchase table using
DELETE FROM ... FROM ...
Use
WHERE Customer.Id = Purchase.CustomerId AND Customer.LastName = N'Smith' AND Purchase.ProductId = 3
