How to remove duplicate MySQL records (but only leave one)-CodePudding

How to remove duplicate MySQL records (but only leave one)

Hello everyone, I have a problem, I have several records with the same ID and I would like to eliminate the duplicate records but leaving only one. Any ideas with a mysql statement?

I have this statement to see the records and the number of duplicates but it doesn't work for me when I use a delete:

SELECT 
    email, 
    COUNT(email)
FROM
    contacts
GROUP BY email
HAVING COUNT(email) > 1;

I use this statement but it only removes a single duplicate record:

DELETE FROM wp_options  WHERE option_id=5 limit 1;

Any way to do it massively?

Update: I am using this statement but it eliminates all duplicate records without leaving one:

DELETE FROM xhi_options
WHERE  option_id IN (SELECT option_id
                FROM   (SELECT option_id
                        FROM   xhi_options
                        GROUP  BY option_id
                        HAVING COUNT(option_id) > 1) AS T)

CodePudding user response：

You can use this to keep the row with the lowest id value

DELETE e1 FROM contacts e1, contacts e2 WHERE e1.id > e2.id AND e1.email = e2.email;

this an example link link 1

or you can change > to < for keep the highest id

DELETE e1 FROM contacts e1, contacts e2 WHERE e1.id < e2.id AND e1.email = e2.email;

this an example link link 2

CodePudding user response：

In your delete statement you use limit 1 option, it means that you will delete only one record with this statement.

Try to fix your code like

DELETE FROM wp_options WHERE option_id=5 limit (dupl_rec_count - 1);