Delete all but one of duplicate rows in mysql

Question

How can I delete duplicate rows but only if both fields are equal. For example.. In the table below only One Atlanta record would be deleted because not only does the City Field match but the Foreign Key Matches as well. But Dallas would not be deleted because of the different foreign key.

+----+-----------------+----------+
| id | City            |     FK   |
+----+-----------------+----------+
| 1  | Los Angeles     |      2   |
| 2  | Dallas          |      5   |
| 3  | Dallas          |      8   |
| 4  | Atlanta         |      12  |
| 5  | Atlanta         |      12  |
| 6  | New York City   |      31  |
+----+-----------------+----------+

In the given example you want only to delete the duplicate Atlanta, but not the Dallas (since the FK is different), is that correct? — Oscar Picchi, Sep 23 '14 at 17:08

score 0 · Answer 1 · answered Sep 23 '14 at 17:32

we get maximum id in case City and FK are same and delete all other entries apart from the max id entry where City and FK are duplicate

DELETE A
FROM TableA A join
     (SELECT MAX(id), City, FK
      from TableA
      group by City,FK
      having count(*) > 1
     ) AA
     on A.City = AA.City
     and A.FK = AA.FK
     and A.id < AA.id

Amazigh.Ca · Answer 2 · 2014-09-23T17:52:37.830

0

Try this:

    Delete from MyTable where id in
        (select T1.id from MyTable T1 where T1.id in
        (select T2.id from MyTable T2 where T2.city = T1.City and T2.fk= T1.fk) )
    and id not in (select MAX(id) from MyTable where id in 
       (select T1.id from MyTable T1 where T1.id in
        (select T2.id from MyTable T2 where T2.city = T1.City and T2.fk= T1.fk)))

edited Sep 23 '14 at 17:52

answered Sep 23 '14 at 17:33

Amazigh.Ca

2,753
2
11
8

score 0 · Answer 3 · edited May 23 '17 at 11:44

Another question here has a very interesting answer that you may want to try. Probably something like this would do the job for you :

ALTER IGNORE TABLE YourTableName ADD UNIQUE INDEX idx_name (City, FK);

Be careful with that : I recommend taking a backup before.

Update

There is a bug with InnoDB and the IGNORE statement. As a workaround you must run set session old_alter_table=1; prior to the ALTER command.

Here is a Fiddle.

Delete all but one of duplicate rows in mysql

3 Answers3