A DELETE statement can include JOIN operations.
It can contain zero, one, or multiple JOIN operations.
The DELETE removes records that satisfy the JOIN conditions.
Remove products that have not sold.
DELETE P
FROM Product P
LEFT JOIN OrderItem I ON P.Id = I.ProductId
WHERE I.Id IS NULL
This problem requires a LEFT JOIN.
P and I are table aliases.
JOIN syntax.
DELETE table-name1 FROM table-name1 JOIN table-name2 ON column-name3 = column-name4 WHERE condition
INNER JOIN syntax.
DELETE table-name1 FROM table-name1 INNER JOIN table-name2 ON column-name3 = column-name4 WHERE condition
JOIN is the same as INNER JOIN; the INNER keyword is optional.
| PRODUCT |
|---|
| Id |
| ProductName |
| SupplierId |
| UnitPrice |
| Package |
| IsDiscontinued |
| ORDERITEM |
|---|
| Id |
| OrderId |
| ProductId |
| UnitPrice |
| Quantity |
DELETE OrderItem
FROM OrderItem
JOIN [Order] on OrderItem.OrderId = [Order].Id
JOIN Product ON Product.Id = OrderItem.ProductId
WHERE OrderNumber = '542379'
AND ProductName = 'Tofu'
Note: This removes a single order item. With that, the TotalAmount value in the Order table is now invalid. To recalculate this value requires a separate UPDATE query.
