Kangaroo Court {concept47 blog}

Getting rid of duplicate Entries in your MySql database

March 27th, 2008

Thanks for Justin Cook for doing all the leg work on this one.

All you have to do is create a new table from the old one, which filters out the duplicate entries

1
2
3

CREATE TABLE new_table AS
SELECT * FROM old_table WHERE 1
GROUP BY [colum_to_remove_duplicates_from];

so as an example

1
2

CREATE TABLE news_new AS
SELECT * FROM news WHERE 1 GROUP BY [title];<span id="more-113"></span>

Now you just have to go in and rename the news db to news_old (for the newbies to this … don’t drop the table immediately, at least until you’re sure the new one works fine).

Then rename news_new to news

Phpmyadmin should make all this easy (click on the “Operations” tab), but in case you are working from the console, here is the syntax you need to know

1

RENAME TABLE new_table TO old_table;

That isn’t all that you have to do, however. The new table will be missing its primary key and auto increment settings as well as any other indexes that you may have had on the old table.
Go in and set the primary key (first) back on the column that had it, then (secondly … the order matters) clear out the default value on it and set it to auto increment.

You can re-add your indexes later … be sure to put a unique index on the column you were having trouble with.

Entry Filed under: developers, mysql