SQL DELETE with JOIN

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.

DELETE JOIN

Example

#

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.

Result:  1 record deleted

Syntax

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.


More Examples

DELETE with JOIN

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
ORDERITEM
Id
OrderId
ProductId
UnitPrice
Quantity
Problem: For order number 542379 remove the Tofu.
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.

Result:  1 record deleted.

You may also like



Guides


vsn 3.1