Home > Mobile >  sql save results in variable and later use it for update
sql save results in variable and later use it for update

Time:02-24

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)
  • Related