Is it possible to delete from multiple tables in the same SQL statement?

Question

It's possible to delete using join statements to qualify the set to be deleted, such as the following:

DELETE J FROM Users U inner join LinkingTable J on U.id = J.U_id inner join Groups G on J.G_id = G.id  WHERE G.Name = 'Whatever' and U.Name not in ('Exclude list') 

However I'm interested in deleting both sides of the join criteria -- both the LinkingTable record and the User record on which it depends. I can't turn cascades on because my solution is Entity Framework code first and the bidirectional relationships make for multiple cascade paths.

Ideally, I'd like something like:

DELETE J, U FROM Users U inner join LinkingTable J on U.id = J.U_id ... 

Syntactically this doesn't work out, but I'm curious if something like this is possible?

Answer 1

Nope, you'd need to run multiple statements.

Because you need to delete from two tables, consider creating a temp table of the matching ids:

SELECT U.Id INTO #RecordsToDelete FROM Users U    JOIN LinkingTable J ON U.Id = J.U_Id ... 

And then delete from each of the tables:

DELETE FROM Users  WHERE Id IN (SELECT Id FROM #RecordsToDelete)  DELETE FROM LinkingTable WHERE Id IN (SELECT Id FROM #RecordsToDelete) 

Answer 2

The way you say is Possible in MY SQL but not for SQL SERVER

You can use of the "deleted" pseudo table for deleting the values from Two Tables at a time like,

 begin transaction;   declare @deletedIds table ( samcol1 varchar(25) );   delete #temp1  output deleted.samcol1 into @deletedIds  from #temp1 t1  join #temp2 t2  on t2.samcol1 = t1.samcol1   delete #temp2  from #temp2 t2  join @deletedIds d  on d.samcol1 = t2.samcol1;   commit transaction; 

For brief Explanation you can take a look at this Link

and to Know the Use of Deleted Table you can follow this Using the inserted and deleted Tables