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

1. You can always set up cascading deletes on the relationships of the tables.

2. You can encapsulate the multiple deletes in one stored procedure.

3. You can use a transaction to ensure one unit of work.

Answer 2

As Aaron has already pointed out, you can set delete behaviour to CASCADE and that will delete children records when a parent record is deleted. Unless you want some sort of other magic to happen (in which case points 2, 3 of Aaron's reply would be useful), I don't see why would you need to delete with inner joins.

Answer 3

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.

See example D here.

Answer 4

Basically, no you have to make three delete statements in a transaction, children first and then parents. Setting up cascading deletes is a good idea if this is not a one-off thing and its existence won't conflict with any existing trigger setup.

Answer 5

In SQL server there is no way to delete multiple tables using join. So you have to delete from child first before delete form parent.

Answer 6

Just wondering.. is that really possible in MySQL? it will delete t1 and t2? or I just misunderstood the question.

But if you just want to delete table1 with multiple join conditions, just don't alias the table you want to delete

this:

DELETE t1,t2 
FROM table1 AS t1 
INNER JOIN table2 t2 ...
INNER JOIN table3 t3 ...

should be written like this to work in MSSQL:

DELETE table1
FROM table1 
INNER JOIN table2 t2 ...
INNER JOIN table3 t3 ...

to contrast how the other two common RDBMS do a delete operation:

http://mssql-to-postgresql.blogspot.com/2007/12/deleting-duplicates-in-postgresql-ms.html

Answer 7

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.

Answer 8

All has been pointed out. Just use either "DELETE ON CASCADE" on the parent table or delete from the child-table to the parent.