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:or probably more efficiently with a join:
and even better with a derived table or a cte (no self-join):