Delete Using Join

The DELETE statement is one of the most fundamental statements on SQL Server.  Just about everyone knows how to use the delete statement.  But did you know that you can delete using a join?  It’s actually pretty easy.

Delete Using Join

To delete from a table using a join condition, you need to first format your statement like a regular select statement.  Instead of using a SELECT statement, you will use a DELETE statement.  After the delete command you will specify the table that you want to delete from in your join.

In the example below, we want to delete all orders for this employee.  I have aliased the Orders table with the letter O.  So my delete statement will say “DELETE O”… where O is the alias for the Orders table.  This will only delete data from the Orders table and not from the Employees table.

DELETE	O
FROM	Orders O
INNER	JOIN Employees E ON E.EmployeeID = O.EmployeeID
WHERE	E.LastName = 'Tester'

One thing to note is that you cannot delete from multiple tables this way.

Reference: http://technet.microsoft.com/en-us/library/ms189835.aspx