MySQL Remove Duplicates (Dupes)

Posted on May 20, 2017 at 10:13 am

Remove MySQL duplicates in place, without making a new table:

ALTER IGNORE TABLE foobar ADD UNIQUE (email);

*** This will keep the oldest duplicate record and erase the newer ones

*** Works if index fits in memory and if MySQL < 5.7.4

*** Works fine with MariaDB 10.1

If it doesn’t work for you, try to use this trick:

set session old_alter_table=1;
ALTER IGNORE TABLE foobar ADD UNIQUE (email);
set session old_alter_table=0;

Updated on November 7, 2017 at 2:51 pm

Other Posts

Updated Posts