问题描述:

I'm trying to delete duplicates from a table which contains cities, there may however be multiple cities with the same name across different countries, example from table:

+------+----------+---------------------+

| locId | country | city |

+------+----------+---------------------+

| 1 | DE | Hamburg |

| 2 | US | Hamburg |

| 3 | DE | Hamburg |

| 4 | DE | Hamburg |

| 5 | US | Hamburg |

+------+----------+---------------------+

Now 1,3,4 are the same (different zip codes, but that doesn't matter, safe to remove) and 2,5 are the same. What query could be run to leave only 1 and 2 in the table and deleting the other duplicates ?

网友答案:
DELETE id FROM table t1, table t2 WHERE t1.country <> t2.country AND t1.city = t2.city
相关阅读:
Top