2

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.

  • What should happen if there are 2 rows with same ida+idb but only one of them has comment 'temp'? Or 6 rows but only one has 'temp'? – ypercubeᵀᴹ Oct 27 '14 at 9:31
  • If there are multiple rows with the same IDA and IDB only the ones with comment = "temp" should be deleted. – Daedalus Mythos Oct 27 '14 at 9:32
  • It's a bit weird. If there are 2+ rows, all with 'temp', they will all be deleted. If there is only one with 'temp', it will stay. – ypercubeᵀᴹ Oct 27 '14 at 9:49
  • If there are 2+ rows for (IDA, IDB) there will only be a single 'temp' comment, defined in the script INSERT-ing. – Daedalus Mythos Oct 27 '14 at 9:57
5

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 ;
  • I'm using the solution with the derived table. thank you very much for your guidance! – Daedalus Mythos Oct 27 '14 at 9:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.