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

相关资源：mysql中错误：1093-You can’t specify target table for update in FROM clause的解决方法