Wednesday, March 18, 2009

mysql delete duplicate rows

mysql delete duplicate rows

FROM tablename as t1,
(SELECT MAX(primary_id) AS dupid,COUNT(*) AS dupcnt
FROM tablename
GROUP BY user_id HAVING dupcnt>1)
AS t2
WHERE t1.uid=t2.dupid;

I believe the script removes only one duplicate entry at a time, I think you have to run it several times to remove all duplications (I advise running it in a php script which then counts duplicates left afterwards) or just run it recursively whilst testing for duplicates.

Will Eaton

No comments: