CODEWITHSUNDEEP
sqlsql-server
mazas17
mazas17

Reputation: 47

Delete from multiple tables in single select

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

Upvotes: 0

Views: 82

Answers (1)

mortb
mortb

Reputation: 9869

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

Upvotes: 1

Related Questions