How to delete all the duplicate records in PHP/Mysql

Question

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, Jul 12 '11 at 08:21
http://stackoverflow.com/questions/659906/how-to-delete-duplicate-records-in-mysql-database — Jacob, Jul 12 '11 at 08:21

score 3 · Answer 1 · edited May 23 '17 at 12:34

3

My preferred solution:

In the future, use unique/primary keys to make sure this doesn't happen again.

edited May 23 '17 at 12:34

Community

answered Jul 12 '11 at 08:22

Michael B

score 2 · Answer 2 · answered Jul 12 '11 at 08:41

2

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.

answered Jul 12 '11 at 08:41

Ray Baxter

score -1 · Accepted Answer · answered Jul 12 '11 at 08:30

-1

DELETE * ,count(*)as n FROMaddgroup by id HAVING n>1 .

It will delete all duplicate records

answered Jul 12 '11 at 08:30

Nitish

-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 http://dev.mysql.com/doc/refman/5.0/en/delete.html . You are not allowed to use GROUP BY in a DELETE statement. – Eljakim Jul 12 '11 at 09:04

3 Answers3