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)

测试用建表语句：

CREATE TABLE `people` (`peopleid` varchar(10) DEFAULT NULL,`rowid` int(10) DEFAULT NULL,`name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;