MySQL removes duplicate rows

Question

I have a comment table whose structure is as follows:

id, name, email, comment 

I have many duplicate comments with the same name and email. I need to remove them, can someone suggest me how can I achieve this using a single request?

thanks

Answer 1

 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 )