昨天在我写了一条查询并删除重复数据,但是保存一条的sql发生了一个错误
DELETE FROM people
WHERE people_id NOT IN
(SELECT MIN(people_id) FROM people GROUP BY people/_no HAVING COUNT(people_no)>1)
错误代码： 1093
You can't specify target table 'info_dossier_case' for update in FROM clause
百度查了之后成
DELETE FROM people
WHERE people_id NOT IN (SELECT c.* FROM
(SELECT MIN(people_id) FROM people GROUP BY people_no HAVING COUNT(people_no)>1)c)
具体的原因呢就是mysql数据库不能在同一张表里同时进行查和删除操作,就有点像list在遍历(不用iterator)时不能进行删除操作
所以解决方法就是把查询出来的数据单独形成一个临时表,再从里面删除就行了
网友评论