Possible Duplicate:
How to delete duplicate records in mysql database?
there are duplicate rows in the mysql table. how find the duplicate rows and delete them.
-
What do you mean by duplicate rows? Can you give the table structure or something.Balanivash– Balanivash2011-07-12 08:21:31 +00:00Commented Jul 12, 2011 at 8:21
-
stackoverflow.com/questions/659906/…Jacob– Jacob2011-07-12 08:21:37 +00:00Commented Jul 12, 2011 at 8:21
Add a comment
|
3 Answers
My preferred solution:
How to delete duplicate records in mysql database?
In the future, use unique/primary keys to make sure this doesn't happen again.
Comments
Define the columns that you want to use to determine duplication and add a unique index on them.
ALTER IGNORE TABLE table_name ADD UNIQUE INDEX (c1,c2,c3);
This has the advantage of preventing future duplicates.
Comments
DELETE * ,count(*)as n FROMaddgroup by id HAVING n>1 .
It will delete all duplicate records
1 Comment
Eljakim
-1 for several reasons: intention is different from original question (he wants to remove the duplicates (and keep the 'original'), you delete everything) but mostly because your MySQL statement is invalid SQL. Check the manual at dev.mysql.com/doc/refman/5.0/en/delete.html . You are not allowed to use GROUP BY in a DELETE statement.
