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!
AWESOME! Dude, ur awesome, seriously. This is the most brilliant solution for this i've ever seen. Thanks!
Quick and simple. TY
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);
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!
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!
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)