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

这个错误就是因为，外面没有包一层，加一层当做一张新表就可以了。