2

I have multiple delete statements to run on SQL Server 2008 R2

DELETE FROM A WHERE A_id in (SELECT B_id FROM B WHERE B_name = 'Target')
DELETE FROM B WHERE B_id in (SELECT B_id FROM B WHERE B_name = 'Target')
DELETE FROM C WHERE C_id in (SELECT B_id FROM B WHERE B_name = 'Target')
DELETE FROM D WHERE D_id in (SELECT B_id FROM B WHERE B_name = 'Target')

Is there a way that I can use a variable to replace the repetitive subquery? Is there such a variable type to hold the subquery or its results?

Thank you

Thom A
  • 88,727
  • 11
  • 45
  • 75
Sean
  • 981
  • 1
  • 9
  • 19

1 Answers1

0

Table variable to hold the IDs to delete...

Declare @BIDs Table
(
  B_Id Int Not Null
)

Insert Into @BIDs (B_Id)
SELECT B_Id FROM B WHERE B_Name = 'Target'

DELETE FROM A WHERE A_id in (SELECT B_id FROM @BIDs)
DELETE FROM B WHERE B_id in (SELECT B_id FROM @BIDs)
DELETE FROM C WHERE C_id in (SELECT B_id FROM @BIDs)
DELETE FROM D WHERE D_id in (SELECT B_id FROM @BIDs)
amit_g
  • 30,880
  • 8
  • 61
  • 118
  • 1
    While true, that doesn't shorten the code nor does it make it better in any way... – Zohar Peled Aug 18 '15 at 04:20
  • There is probably not much that can be done here but I think it is slightly better. This refactor removes the violation of DRY and possibly little more efficient when the SQL server would have to query the table B once vs each time for every delete. Again, depending upon the number of rows in table B, indexes on column B_name, that efficiency might not be viable/needed. For me, the most important reason would be to not have to repeat the same query for each delete - DRY. – amit_g Aug 19 '15 at 19:11