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

最新推荐文章于 2024-07-25 11:42:08 发布

原创最新推荐文章于 2024-07-25 11:42:08 发布 · 321 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#MySQL

数据库专栏收录该内容

5 篇文章

订阅专栏

本文解决了MySQL中因尝试在同一表查询和更新而导致的错误“You can't specify target table for update in FROM clause”。通过调整SQL语句结构，成功实现了对重复记录的删除。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

来自于解决重复数据项时遇到的问题，详细可查看：点击打开链接

问题：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;