mysql delete duplicate rows
November 5th, 2007 Posted in MYSQLDELETE tablename
FROM tablename,
(SELECT MAX(uid) AS dupid,COUNT(uid) AS dupcnt
FROM tablename
GROUP BY id,url HAVING dupcnt>1)
AS dups
WHERE tablename.uid=dups.dupid;
DELETE tablename
FROM tablename,
(SELECT MAX(uid) AS dupid,COUNT(uid) AS dupcnt
FROM tablename
GROUP BY id,url HAVING dupcnt>1)
AS dups
WHERE tablename.uid=dups.dupid;
2 Responses to “mysql delete duplicate rows”
By Attila UYANIK on Feb 10, 2008
this script delete all duplicate rows… I need just delete one of double rows…. help me
By admin on Mar 8, 2008
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