How to delete duplicate records in a mysql table?

Question

Possible Duplicate:
How to delete duplicate rows from a MySQL table

I've a mysql table with duplicate records and I want to delete all duplicate records. How can I do this? My table has only a few columns and 1 column can have duplicate records. It's a about a registration of user and I don't want an e-mail to be registerd twice. I cannot check the e-mail before it's entered because it would complicate the registration process and it has to be as simple as possible.

The title says "duplicate records", but the question has "duplicate columns". Can you give clear example? — galymzhan, Sep 02 '12 at 11:22
You cannot have two columns with the same name; so do you have duplicate rows? — Burhan Khalid, Sep 02 '12 at 11:22
@BurhanKhalid: The example you linked is about all the other columns identical, too. I need only 1 column to be dupelicate? — Micromega, Sep 02 '12 at 11:26
Can you update the question with a sample of your duplicated data and what you want to the result to be? — Burhan Khalid, Sep 02 '12 at 11:28
Check out http://www.databasejournal.com/features/mysql/article.php/10897_2201621_2/Deleting-Duplicate-Rows-in-a-MySQL-Database.htm — Akash KC, Sep 02 '12 at 11:28
Records may be duplicate in 1 column, but have different values in other columns. You need some criteria to determine which records to delete. — galymzhan, Sep 02 '12 at 11:29
If you have two records with the same email, how do you know which one to delete? — Burhan Khalid, Sep 02 '12 at 11:54

score 0 · Accepted Answer · answered Sep 02 '12 at 14:20

0

You can perform a self-join in the multiple-table form of DELETE:

DELETE u1
FROM   users u1 JOIN users u2 USING (email)
WHERE  u1.timestamp < u2.timestamp

answered Sep 02 '12 at 14:20

eggyal

122,705
18
212
237

Thanks. What is using means? Does this work with 2 duplicated records only or with many? – Micromega Sep 02 '12 at 14:27
@Chiyou: For each `email`, it will delete all matching records except the most recent. – eggyal Sep 02 '12 at 14:34

How to delete duplicate records in a mysql table?

1 Answers1