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
CREATE TABLE news_new AS SELECT * FROM news WHERE 1 GROUP BY [title]; <a href="http://www.concept47.com/austin_web_developer_blog/developers/getting-rid-of-duplicate-entries-in-your-mysql-database/#more-113" class="more-link">Continue reading <span class="meta-nav">→</span></a>
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
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.