Sql-server – Delete from table where two of the three keys in another select

deletesql serversql-server-2005

I have tableA which has 4 columns:

IDA (bigint), IDB (int), IDC (varchar(255)) and comment (varchar(255)). The primary key is (IDA, IDB, IDC).

I'm required to delete the records where the count of (IDA, IDB) is above 1 and the comment is "temp". I've come up with the following SQL statement:

DELETE FROM tableA WHERE IDA, IDB IN
(SELECT IDA, IDB FROM tableA GROUP BY IDA, IDB HAVING COUNT(*) > 1)
AND comment = 'temp'

But I get the following error:
"An expression of non-boolean type specified in a context where a condition is expected, near ','. […]"

Working on Microsoft SQL Server Enterprise Edition v9.00.3042.00, i.e. SQL Server 2005 Service Pack 2

I'm rather new and appreciate any help/suggestions.

Best Answer

Your code is valid SQL but not in SQL-Server. They haven't yet implemented the syntax (a,b) IN ((1,11), (2,12), ...) or (a,b) IN (SELECT expa, expb....).

You can work around with either an EXISTS subquery:

DELETE FROM tableA AS a
WHERE EXISTS
      ( SELECT 1
        FROM tableA AS b
        WHERE b.IDA = a.IDA AND b.IDB = a.IDB 
        HAVING COUNT(*) > 1
      ) 
  AND a.comment = 'temp' ;

or probably more efficiently with a join:

DELETE a
FROM tableA AS a
  JOIN
    ( SELECT 
      FROM tableA 
      GROUP BY IDA, IDB
      HAVING COUNT(*) > 1
   ) AS b
     ON  b.IDA = a.IDA AND b.IDB = a.IDB 
WHERE a.comment = 'temp' ;

and even better with a derived table or a cte (no self-join):

WITH dups AS
  ( SELECT comment, 
           cnt = COUNT(*) OVER (PARTITION BY IDA, IDB)
     FROM tableA 
  ) 
DELETE 
FROM dups
WHERE comment = 'temp' 
  AND cnt > 1 ;