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;