Delete a record from several tables using a specific value

Question

Today, I want to delete all test record from several tables in sql server. Here is what I want to achieve..

Select ID from sourceTable where acctType='S' and acctroot<>0

Suppose this query returns 5 rows..

   | ID|
    ---
   | 1 |
   | 2 |
   | 3 |
   | 4 |
   | 5 |

Now These are the tables that I need to clean

delete from tmpA where ID=1
delete from tmpB where acctID=1
delete from tmpC where userID = 1
delete from tmpD where sID=1
.
.
.
delete from tmpA where ID=2
delete from tmpB where acctID=2
delete from tmpC where userID =2
delete from tmpD where sID=2

Can I loop through source table and extract the ID and then delete from the tables? I know I could use joins but I want to do that using While loop..

Answer 1

Simple as that

... WHERE ID IN (Select ID from sourceTable where acctType='S' and acctroot<>0)

Like this:

delete from tmpA where ID IN (Select ID from sourceTable where acctType='S' and acctroot<>0)
delete from tmpB where acctID IN (Select ID from sourceTable where acctType='S' and acctroot<>0)
delete from tmpC where userID IN (Select ID from sourceTable where acctType='S' and acctroot<>0)
delete from tmpD where sID IN (Select ID from sourceTable where acctType='S' and acctroot<>0)