SQL---mysql删除重复数据
开发时，经常会有清理数据库中重复数据的需求，比如下面这张表report_apply ：
我们需要删除report_name重复的数据，具体步骤如下：
-- 重复数据
SELECT report_name from report_apply GROUP BY report_name HAVING count(report_name) > 1
-- 重复数据中最小的id留下
SELECT MIN(id) from report_apply GROUP BY report_name HAVING count(report_name) > 1
-- 查询所有重复数据并排除最小的id 此为需要删除的数据
SELECT id from report_apply where report_name in (
SELECT report_name from report_apply GROUP BY report_name HAVING count(report_name) > 1
)
and id NOT in(
SELECT MIN(id) from report_apply GROUP BY report_name HAVING count(report_name) > 1
)
-- 直接delete from删除会报错，所以包一层，当做一张临时表
SELECT id from (
SELECT id from report_apply where report_name in (
SELECT report_name from report_apply GROUP BY report_name HAVING count(report_name) > 1
)
and id NOT in(
SELECT MIN(id) from report_apply GROUP BY report_name HAVING count(report_name) > 1
)
) as result
-- 然后delete删除即可
DELETE from report_apply where id in (
SELECT id from (
SELECT id from report_apply where report_name in (
SELECT report_name from report_apply GROUP BY report_name HAVING count(report_name) > 1
)
and id NOT in(
SELECT MIN(id) from report_apply GROUP BY report_name HAVING count(report_name) > 1
)
) as result
)You can't specify target table 'report_apply ' for update in FROM clause
这个错误就是因为，外面没有包一层，加一层当做一张新表就可以了。
本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2018年03月08日，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读