Delete from multiple tables in single select

Question

I want to delete record from table foo, but it has many FK's with reference constraints to other tables. I don't have CASCADE set on. Can I do it one query? Something like:

delete f, b
from foo f
left join bar b on b.fooID = f.ID
where f.ID = 11764

No. It can't be done in one query if you don't have cascade set on. You'll need to delete the rows in the table that references your table first. — mortb, Oct 23 '17 at 06:31
then maybe there is some other more practical way instead of manually changing allias after `delete` statement? — mazas17, Oct 23 '17 at 06:36
Possible duplicate of [How do I use cascade delete with SQL Server?](https://stackoverflow.com/questions/6260688/how-do-i-use-cascade-delete-with-sql-server) — Sudipta Mondal, Oct 23 '17 at 06:48

mortb · Answer 1 · 2017-10-23T11:43:28.760

You need to convert your query into something like:

DECLARE @fooId INT = 11764;

DELETE FROM bar 
WHERE fooID = @fooID

DELETE FROM foo
WHERE ID = @fooId

By first declaring the @fooId variable the code easier to reuse if you want to delelte more than one item.

If you want to delete many items, according to some condition, you could declare a table variable where you put the ids:

DECLARE @deleteItems TABLE
(
    ID INT
);

INSERT INTO @deleteItems (ID)
    SELECT ID FROM foo WHERE <your conditions here>

DELETE FROM Bar 
WHERE FooID in (SELECT ID FROM @deleteItems)

DELETE FROM Foo 
WHERE ID in (SELECT ID FROM @deleteItems)

It might also be appropriate to put BEGIN TRANSACTION at the beginning and COMMIT TRANSACTION at the end to make sure that the updates are atomic

Delete from multiple tables in single select

1 Answers1