How to delete duplicate records from mysql table but having 1 record

Question

I have a table in mysql which have 1319 duplicate records how can I delete duplicate values but have 1 record saved?

Answer 1

There are many options:

First use this kind of query:

DELETE FROM comments c1 
WHERE EXISTS (SELECT * 
              FROM comments c2 
              WHERE c2.id <> c1.id 
                AND c2.NAME = c1.NAME 
                AND c2.email = c1.email 
                AND c2.COMMENT = c1.COMMENT)
  AND c1.id <> (SELECT MIN(c2.id) 
                FROM comments c2 
                WHERE c2.NAME = c1.NAME 
                  AND c2.email = c1.email 
                  AND c2.COMMENT = c1.COMMENT)

Second:

Create another blank table of same structure and insert into second table using group by on all columns or few unique columns.

Third:

Apply a unique key using alter ignore table.

Answer 2

try this.....

DELETE FROM tbl WHERE id NOT IN ( SELECT * FROM(SELECT id FROM tbl GROUP BY col1, col2, ) AS p)