4

I have a comments table, whose structure is as:

id, name, email, comment

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

Thanks

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
I-M-JM
  • 15,732
  • 26
  • 77
  • 103
  • possible duplicate of [Remove Duplicate Rows Leaving Oldest Row Only?](http://stackoverflow.com/questions/3671607/remove-duplicate-rows-leaving-oldest-row-only) – OMG Ponies May 05 '11 at 06:25

1 Answers1

7
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
)
rodrigo-silveira
  • 12,607
  • 11
  • 69
  • 123
lweller
  • 11,077
  • 3
  • 34
  • 38