CODEWITHSUNDEEP
sql-servert-sql
Rubi
Rubi

Reputation: 43

Better way of handling many delete statements

I have a long script for deleting some specific data from a lot (more than 100+) of tables and it looks something like this:

...

Delete from Table_name WHERE Company not in ('Company1','Company2')
Delete from Table_name WHERE Company not in ('Company1','Company2')
Delete from Table_name WHERE Company not in ('Company1','Company2')
Delete from Table_name WHERE Company not in ('Company1','Company2')

...

I want to change this so I don't have to change the variables on every line, I want to be able to set a where statement in the beginning, and that will change all the delete lines

declare .....something something
SELECT CompanyID
FROM _Company
WHERE  Company in ('Company1','Company2') -- I want to change this where statement only

Delete from Table_name WHERE Company not in (variable)
Delete from Table_name WHERE Company not in (variable)
Delete from Table_name WHERE Company not in (variable)
Delete from Table_name WHERE Company not in (variable)

Upvotes: 2

Views: 77

Answers (2)

Dave
Dave

Reputation: 359

Another solution would be to create a cursor over the select statement and for each resulting entity run the delete statements with proper variables.

Upvotes: 0

AakashM
AakashM

Reputation: 63358

You want a table variable. Alter column names, datatypes as necessary:

DECLARE @idsToKeep TABLE ( CompanyID int );

INSERT @idsToKeep
SELECT CompanyID
FROM _Company
WHERE Company IN ('Company1','Company2'); -- change this one place

DELETE Table_name1 WHERE CompanyID NOT IN ( SELECT CompanyID FROM @idsToKeep ); 
DELETE Table_name2 WHERE CompanyID NOT IN ( SELECT CompanyID FROM @idsToKeep ); 
DELETE Table_name3 WHERE CompanyID NOT IN ( SELECT CompanyID FROM @idsToKeep ); 
-- etc

Upvotes: 3

Related Questions