sql save results in variable and later use it for update-CodePudding

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)