How do I delete from multiple tables using INNER JOIN in SQL server

Question

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?

Answer 1

You can try using the "deleted" pseudo table in this example like this:

begin transaction;

 declare @deletedIds table ( id int );

   delete from t1

   output deleted.id into @deletedIds

   from table1 as t1

    inner join table2 as t2

      on t2.id = t1.id

    inner join table3 as t3

      on t3.id = t2.id;

 delete from t2

   from table2 as t2

    inner join @deletedIds as d

      on d.id = t2.id;

    delete from t3

   from table3 as t3 ...

commit transaction;

You can also do 'output deleted.' on the second delete as well, if you want something to join on for the third table.You can also do inserted.* on an insert statement, and both should be inserted.* and deleted.* on an update statement.

Note: You need to consider adding the trigger on table1 to delete from table2 + 3.You'll be inside the implicit transaction, and will also have the "inserted." and "deleted." pseudo-tables available.