By Tay


2019-06-12 07:21:56 8 Comments

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..

5 comments

@Marc Guillot 2019-06-12 07:38:17

Another way to ensure that your ID exists on sourcetable is with an INNER JOIN.

DELETE T
FROM tmpA T
     INNER JOIN sourcetable S ON S.id = T.id AND S.accttype = 'S' AND S.acctroot  <> 0  

@Thangadurai.B 2019-06-12 07:40:09

Try this,

Declare @Temp_Table Table (Id int identity(1,1),Table_Name varchar(30),Column_Name varchar(30))

insert into @Temp_Table
select 'tmpA','ID'
union all
select 'tmpB','acctID'
union all
select 'tmpC','userID'
union all
select 'tmpD','sID'

Declare @Counter int=1
        ,@Tot_Count int=0
        ,@Table_Name varchar(30)=''
        ,@Column_Name varchar(30)=''

select @Tot_Count=count(1) 
from @Temp_Table

While @Tot_Count >= @Counter
Begin

         Select @Table_Name=Table_Name
                ,@Column_Name=Column_Name
           From @Temp_Table
          Where [email protected]

           Exec
           (
                '
                 Delete A 
                   From '[email protected]_Name+' A
             inner join sourceTable B 
                     on A.'[email protected]_Name+'=B.ID
                  where B.acctType=''S'' 
                    and B.acctroot<>0
                '
           )

           Set @Counter+=1
End

@Red Devil 2019-06-12 07:26:09

You don't need while loop for this

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

@hims056 2019-06-12 07:25:27

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)

@Tay 2019-06-12 07:27:29

oops sorry for asking this question :D

@PSK 2019-06-12 07:24:54

You don't need a WHILE loop, you can do it like following using WHERE EXISTS.

DELETE A 
FROM   tmpa a 
WHERE  EXISTS (SELECT 1 
               FROM   sourcetable s 
               WHERE  s.accttype = 'S' 
                      AND s.acctroot <> 0 
                      AND s.id = a.id) 

You need to do the same for other tables also.

Related Questions

Sponsored Content

37 Answered Questions

29 Answered Questions

[SOLVED] Finding duplicate values in a SQL table

  • 2010-04-07 18:17:29
  • Alex
  • 2565207 View
  • 1734 Score
  • 29 Answer
  • Tags:   sql duplicates

20 Answered Questions

7 Answered Questions

27 Answered Questions

[SOLVED] SQL select only rows with max value on a column

22 Answered Questions

[SOLVED] SQL update from one Table to another based on a ID match

23 Answered Questions

[SOLVED] Insert into ... values ( SELECT ... FROM ... )

11 Answered Questions

[SOLVED] SQL Server: How to Join to first row

11 Answered Questions

15 Answered Questions

[SOLVED] Select n random rows from SQL Server table

  • 2009-05-11 16:19:11
  • John M Gant
  • 336671 View
  • 291 Score
  • 15 Answer
  • Tags:   sql sql-server random

Sponsored Content