CODEWITHSUNDEEP
sqlsql-server
tray
tray

Reputation: 261

SQL Server - help deleting rows with a multiple where clause using a sub query

I am having some trouble with a SQL delete. SQL Server doesn't like have multiple parameters in the where clause to delete rows from table_02 (subquery is table 1). Any help on this would greatly be appreciated.

thanks.

DELETE FROM table_02
        WHERE (col_1,col_2,col_3,col_4)
        IN (            
            SELECT col_1,col_2,col_3,col_4 
                    FROM table_01
                    GROUP BY
                    col_1,col_2,col_3,col_4
                    HAVING SUM(CASE WHEN col_1<6 THEN col_2*-1 ELSE col_2 END)=0
           )

Upvotes: 3

Views: 4043

Answers (2)

Martin Smith
Martin Smith

Reputation: 453668

You can rewrite IN as an EXISTS

DELETE 
FROM table_02
WHERE  EXISTS(SELECT *
              FROM   table_01
              WHERE  table_02.col_1 = table_01.col_1
                     AND table_02.col_2 = table_01.col_2
                     AND table_02.col_3 = table_01.col_3
                     AND table_02.col_4 = table_01.col_4
              HAVING SUM(CASE
                           WHEN col_1 < 6 THEN col_2 * -1
                           ELSE col_2
                         END) = 0)

Upvotes: 5

ajh4
ajh4

Reputation: 81

Delete a  
from table1 a 
Inner Join table2 b 
on  a.col = b.col 
WHERE ...

Upvotes: 1

Related Questions