MySQL Delete dupes

My favorite way of deleting dupes (taken from the honorable #mysql on freenode)


If you have a unique ID and the name may contain duplicates then

DELETE t1 FROM table1 AS t1 JOIN table1 AS t2 ON t1.id>t2.id AND t1.name=t2.name;

If you have other fields that need to be taken into consideration extend the join as needed. If it fails on a myisam table see http://bugs.mysql.com/bug.php?id=28837

Comments

Popular posts from this blog

Building XtraBackup for Mac OSX

How to compile Percona XtraDB Cluster 5.6 on Ubuntu 14.04 Trusty Tahr

My custom optimized build of PHP 5.5 & PHP 5.4