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

Question

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)

Can you include the full table structure for `contacts`? Is there any column(s) which can differentiate duplicates? — Tim Biegeleisen, Mar 16 '22 at 01:48

AdityaDees · Accepted Answer · 2022-03-16T02:00:50.480

5

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

edited Mar 16 '22 at 02:00

answered Mar 16 '22 at 01:53

AdityaDees

1,022
18
39

Thanks, it worked perfect for me, create an auto-incrementing field in the table and use your statement – María perez Kusky Mar 18 '22 at 11:16

score 0 · Answer 2 · answered Mar 16 '22 at 01:36

0

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);

answered Mar 16 '22 at 01:36

Nerower

193
12

Have you tried this? IIRC the `LIMIT` clause does not allow expressions. – Bill Karwin Mar 16 '22 at 03:27
@BillKarwin, I wrote `limit (dupl_rec_count - 1)` as an example because I don't know the actual number of records, of course he should pass a single value – Nerower Mar 16 '22 at 11:12

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

2 Answers2