Earn income with your data and sql skills
Sign up and we'll send you the best freelance opportunities straight to your inbox.
We're building the largest self-service freelancing marketplace for people like you.

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



Earn income with your data and sql skills
Sign up and we'll send you the best freelance opportunities straight to your inbox.
We're building the largest self-service freelancing marketplace for people like you.

Guides


vsn 3.1