I am using MSSQL2017
Lets say I have a Table called Partner, this Table has an ID and a delete Flag. And I have a second Table called PatnerAdress, this Table has a PatnerID and some data. And lets say there are 5 more Tables like PatnerComunication (PartnerID, Email, someData...), PartnerSomething1, PartnerSomething2, PartnerSomething3.
Now I would like to do something like this:
Is this even possible and if yes what Type do I have to use for my @IDsToDelete?
DECLARE @IDsToDelete someTypeIDontKnow;
SET @IDsToDelete = (SELECT ID FROM Partner WHERE delete = 1)
DELETE PatnerAdress WHERE PartnerID IN @IDsToDelete
DELETE PatnerComunication WHERE PartnerID IN @IDsToDelete
DELETE PartnerSomething1 WHERE PartnerID IN @IDsToDelete
DELETE PartnerSomething2 WHERE PartnerID IN @IDsToDelete
DELETE PartnerSomething3 WHERE PartnerID IN @IDsToDelete
CodePudding user response:
DECLARE @t TABLE (v varchar(64));
insert into @t (v)
SELECT ID from Partner WHERE delete = 1
DELETE PatnerAdress WHERE PartnerID in (select v from @t)
DELETE PatnerComunication WHERE PartnerID in (select v from @t)
DELETE PartnerSomething1 WHERE PartnerID in (select v from @t)
DELETE PartnerSomething2 WHERE PartnerID in (select v from @t)
DELETE PartnerSomething3 WHERE PartnerID in (select v from @t)
CodePudding user response:
you can use table variable. it gives as mush as value as you want. then you can use Join in Delete meaning join your table with table variable.
DECLARE @a table (IDsToDelete int )
insert into @a
(SELECT ID FROM Partner WHERE delete = 1)
--delete statment
delete PA from
PatnerAdress PA
join @a a on a.IDsToDelete=PA.PartnerID
or simply Delete
DELETE PatnerAdress WHERE PartnerID IN
(select * from @a)
In addition you can use temporary table or global temporary table
DROP TABLE IF EXISTS #temptable ;
SELECT ID
into #temptable --create temporary table
FROM Partner WHERE delete = 1
DELETE PatnerAdress WHERE PartnerID IN
(select * from #temptable)