This is an extremely quick and painless way to remove duplicate rows (tuples) from a MySQL database table. The best part of it is that it requires no programming or PHP coding whatsoever, it can all be done with three manual SQL queries! Note: this only works on MySQL 3.23 and above. But I have a hard time imagining anyone running an older version than that anyways!
So, there are 3 steps, and therefore 3 SQL statements:
Step 1: Move the non duplicates (unique tuples) into a temporary table
CREATE TABLE new_table as
SELECT * FROM old_table WHERE 1 GROUP BY [column to remove duplicates by];
Step 2: delete delete the old table
We no longer need the table with all the duplicate entries, so drop it!
DROP TABLE old_table;
Step 3: rename the new_table to the name of the old_table
RENAME TABLE new_table TO old_table;
And of course, don't forget to fix your buggy code to stop inserting duplicates!
81 Comments
A safer version would be to rename the old table to a backup instead of dropping it.
Step 2)
RENAME TABLE old_table TO old_table_backup;
Do you really see this as a valid way to remove duplicates? Why would you remove a table when you only want to remove duplicates?
"Sid Debgupta said,
This will not work for multiple column uniqueness"
Not true….You can do this:
CREATE TABLE NEWTABLE as
SELECT * FROM OLDTABLE WHERE 1 GROUP BY column1,column2;
AWESOME! Dude, ur awesome, seriously. This is the most brilliant solution for this i've ever seen. Thanks!
Quick and simple. TY
[…] want something simpilar and are not necessarily interested in what record might have better data. Follow these instructions here on creating a new table to get rid of duplicate rows Tags: SQL […]
[…] http://www.justin-cook.com/wp/2006/12/12/remove-duplicate-entries-rows-a-mysql-database-table/ […]
Also remember to set that column to unique to ensure there aren't any duplicates.
Thanks This helped me eliminate like 4000 rows in a table!!! GREAT code!
this is best delete query
You can just do this:
create table new_table1 (select distinct * from old_table);
[…] source […]
You could try this query for finding duplicate rows in mysql.
Thanks for this! I had to use it as the duplicate records in my DB were preventing me from altering a Primary Key
Cheers!
[…] Sesuai dengan judulnya, berikut adalah langkah – langkah yang bisa kalian pakai untuk menghapus data / record yang sama dalam suatu tabel di database MySQL hanya dengan satu Query saja. Pengetahuan ini akudapat dari http://www.justin-cook.com/wp/2006/12/12/remove-duplicate-entries-rows-a-mysql-database-table/ […]
Thanks! worked like charm. Else we were thinking of writing a php script to calculate hash, group by etc etc…
This is so simple!!
ALTER IGNORE TABLE table_name ADD UNIQUE KEY(duplicate_field_name)
hey,i need a help in php.
i need to check for same email id in the database ,if that id is exists,i need to replace with new id .For example if i have record like
vj@c.com,vj@c.com,vj@c.com…..i need to change 2nd and 3rd id into vj1@c.com and vj2@c.com….can u help me with the code??
hello friends,help me on this
Create table person(p_id varchar(10) unique,name_s varchar(20))
I have created a table person and set p_id as unique.Now i want to delete the Unique for the P_id.. how can i do that in sql server… reply me asap
hello friends,help me on this
Create table person(p_id varchar(10) unique,
name_s varchar(20))
I have created a table person and set p_id as unique.
Now i want to delete the Unique for the P_id..
how can i do that in sql server… reply me asap
Great man!
[…] отÑюда:Remove duplicate entries / rows a mySQL database table Ð—Ð°Ð¿Ñ€Ð¾Ñ MySql Ð´Ð»Ñ Ð²Ñ‹Ñ‚Ð°ÑÐºÐ¸Ð²Ð°Ð½Ð¸Ñ Ð²Ñех повторÑющихÑÑ Ð·Ð°Ð¿Ð¸Ñей: […]
It works great..got what I wanted.
Thanks for sharing
Was a painless procedure
GENIUS!!!
Many THANKS for the big help 🙂
This helped a bunch. Thanks!
I experienced this problem when trying to set a column as my primary key. Your solution was perfect. Thank you very much.
Thank you so much this has saved me hours of work and a massive headache!
Great Post, I was going about this is another direction, but it seems like your's is a much better way — I was using this to filter duplicate city, and state postal code information on over 200,00 cities some duplicates some not – it helped in no time – much quicker then the way I was using it. I was creating a virtual table and just checking but this is great! – thx mate. have a great one.
this will prevent more duplicate in future
ALTER IGNORE TABLE employee ADD UNIQUE KEY(employee_id)
[…] you just wish to remove any duplicate entries then take a look at Justin Cook's article on the subject. Share and Enjoy: These icons link to social bookmarking sites where readers can […]