使用sql删除重复行

我正在尝试从 mysql 表中删除重复的行。我尝试了多个查询，但不断收到此错误：#1093 - You can't指定目标表“usa_city”以在 FROM 子句中进行更新

该表如下所示：

usa_city
--------
id(pk)
id_state
city_name

我厌倦的查询是：

DELETE FROM usa_city
WHERE id NOT IN
(
SELECT MIN(id)
FROM usa_city
GROUP BY city_name, id_state
)

And:

DELETE
FROM usa_city
WHERE usa_city.id IN

-- List 1 - all rows that have duplicates
(SELECT F.id
FROM usa_city AS F
WHERE Exists (SELECT city_name, id_state, Count(id)
FROM usa_city
WHERE usa_city.city_name = F.city_name
   AND usa_city.id_state = F.id_state
GROUP BY usa_city.city_name, usa_city.id_state
HAVING Count(usa_city.id) > 1))
AND usa_city.id NOT IN

-- List 2 - one row from each set of duplicate
(SELECT Min(id)
FROM usa_city AS F
WHERE Exists (SELECT city_name, id_state, Count(id)
FROM usa_city
WHERE usa_city.city_name = F.city_name
   AND usa_city.id_state = F.id_state
GROUP BY usa_city.city_name, usa_city.id_state
HAVING Count(usa_city.id) > 1)
GROUP BY city_name, id_state);

提前致谢。

尝试先选择重复项，然后删除它们

DELETE FROM usa_city WHERE city_id IN
(
SELECT city_id FROM usa_city
GROUP BY city_name, id_state
HAVING count(city_id) > 1
)

希望能帮助到你！！！

修改：根据评论，如果你想保留一项记录，您可以进行连接并保留最低值

DELETE c1 FROM usa_city c1, usa_city c2 WHERE c1.id < c2.id AND 
(c1.city_name= c2.city_name AND c1.id_state = c2.id_state)

请务必在执行上述查询之前进行备份...