Exists Count(*)

Question

i am trying to do as below its giving syntax error please help

DELETE FROM table 
WHERE col1 = 2
AND EXISTS ( 
               SELECT COUNT(*) 
               FROM table 
               WHERE col1 = 3
           ) > 2 ; 

i need to do a delete only if the cout is greater than 2

Answer 1

The following is meaningless ... EXISTS (...) > 2.

Answer 2

There's two logical conditions in the where: an exists, and a > 2. Remove one of them, like:

DELETE  table 
WHERE   col1  = 2
        AND 
        ( 
        SELECT COUNT(*) 
        FROM table 
        WHERE col1 = 3
        ) > 2

Answer 3

You could just miss out the EXISTS ?

Answer 4

It is unclear what you are actually trying to do, and it's no easier for us to understand an invalid syntax than it is for SQL-server.

HAVING clause will allow you to filter the results based on the count:

SELECT COUNT(*) 
FROM table 
WHERE col1 = 3
HAVING COUNT(*) > 2

But reading between the lines it appears to are trying to delete duplicates from a table, which I'd do with:

DELETE FROM table 
WHERE (id, col1) NOT IN 
    (SELECT 
        id, 
        MAX(col1)
    FROM table
    GROUP BY id
);