用SQL删除重复的行 [英] Deleting duplicate rows with sql
本文介绍了用SQL删除重复的行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试从mysql表中删除重复的行.我已经尝试了多个查询,但仍然不断出现此错误:#1093-您无法在FROM子句中指定目标表'usa_city'进行更新
I am trying to delete duplicate rows from my mysql table. I've tried multiple queries but I am keep on getting this error: #1093 - You can't specify target table 'usa_city' for update in FROM clause
该表如下所示:
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
)
并且:
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);
谢谢.
推荐答案
尝试首先选择重复项,然后将其删除
Try to select the duplicates first, the delete them
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
)
希望有帮助!
已修改:根据评论,如果您想保留一条记录,则可以加入并保持最低值
MODIFIED: Based on the comment, if you want to keep one record, you can make a join and keep the lowest value
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)
在执行上面的查询之前,请确保进行备份...
Be sure to make a backup before executing the query above...
这篇关于用SQL删除重复的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文