How do I delete from multiple tables using INNER JOIN in SQL server
In MySQL you can use the syntax
DELETE t1,t2
FROM table1 AS t1
INNER JOIN table2 t2 ...
INNER JOIN table3 t3 ...
How do I do the same thing in SQL Server?
You can take advantage of the "deleted" pseudo table in this example. Something like:
begin transaction;
declare @deletedIds table ( id int );
delete t1
output deleted.id into @deletedIds
from table1 t1
join table2 t2
on t2.id = t1.id
join table3 t3
on t3.id = t2.id;
delete t2
from table2 t2
join @deletedIds d
on d.id = t2.id;
delete t3
from table3 t3 ...
commit transaction;
Obviously you can do an 'output deleted.' on the second delete as well, if you needed something to join on for the third table.
As a side note, you can also do inserted.* on an insert statement, and both inserted.* and deleted.* on an update statement.
EDIT: Also, have you considered adding a trigger on table1 to delete from table2 + 3? You'll be inside of an implicit transaction, and will also have the "inserted." and "deleted." pseudo-tables available.
You can always set up cascading deletes on the relationships of the tables.
You can encapsulate the multiple deletes in one stored procedure.
You can use a transaction to ensure one unit of work.
You can use JOIN syntax in FROM clause in DELETE in SQL Server but you still delete from first table only and it's proprietary Transact-SQL extension which is alternative to sub-query.
From example here:
-- Transact-SQL extension
DELETE
FROM Sales.SalesPersonQuotaHistory
FROM Sales.SalesPersonQuotaHistory AS spqh INNER JOIN
Sales.SalesPerson AS sp ON spqh.BusinessEntityID = sp.BusinessEntityID
WHERE sp.SalesYTD > 2500000.00;