MySQL：You can't specify target table for update in FROM clause

问题：You can't specify target table for update in FROM clause

含义：不能在同一表中查询的数据作为同一表的更新数据。

注意：这个问题只出现于mysql，mssql和oracle不会出现此问题。

delete from people 
where peopleid in (select peopleid from people group by peopleid having count(peopleid)>1)
and rowid not in (select MIN(rowid) from people group by peopleid having count(peopleid)>1)

修改后代码：

delete from people 
where peopleId  in (SELECT a.peopleid FROM
(select  peopleId  from people  group  by  peopleId   having  count(peopleId) > 1) a)
and rowid not in (SELECT b.rowid FROM
(select MIN(rowid) rowid from people  group by peopleId  having count(peopleId )>1) b)

查询数据结果集中再套一层 

PS：感谢Cap_ZZ   https://blog.csdn.net/qq_38481999/article/details/80916034#commentBox

转载于:https://www.cnblogs.com/yugb/p/10013444.html