Delete using group by with multiple columns

Question

I want to delete all the rows which are returned by this query.

SELECT col1, col2, col3 FROM myTable GROUP BY col1, col2, col3 HAVING count(*) > 1;

I tried this, but it gives me a syntax error.

DELETE FROM myTable WHERE col1, col2, col3 IN ( 
                       SELECT col1, col2, col3 FROM   ( 
                              SELECT   col1, col2, col3 FROM myTable 
                              GROUP BY col1, col2, col3 HAVING count(*) > 1 ) 
                            t );

Answer 1

Use an INNER JOIN with your table

DELETE t1 FROM myTable t1
        INNER JOIN ( 
                       SELECT col1, col2, col3 FROM   ( 
                              SELECT   col1, col2, col3 FROM myTable 
                              GROUP BY col1, col2, col3 HAVING count(*) > 1 ) 
                            t )
    t2 ON t2.rcol1 = t1.rcol1 AND t2.col2 = t1.col AND t2.col3 = t1.col3;

But you you should test it on a test database, because i don't think that your select identifies the right rows, better would be to have a UNIQUE column, that would identify the correct rows, because this would delete all rows