Remove duplicate MySQL records and keep only one

Question

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

Hi everyone, I have a problem, I have several records with the same ID and I want to remove duplicate records but keep only one. Any ideas on using mysql statements?

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

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

I use this statement, but it only removes one duplicate record:

DELETE FROM wp_options WHERE option_id=5 limit 1;

Is there a way to batch process?

Update: I'm using this statement, but it removes all duplicate records without keeping 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)

Answer 1

In your delete statement, you used the limit 1 option, which means you will only delete one record.

Try fixing your code like this:

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

Answer 2

You can use this method 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 is a sample link Link 1

Or you can change > to < to keep the highest id

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

This is a sample link Link 2