MySQL can’t specify target table for update in FROM clause

最新推荐文章于 2024-08-15 09:31:54 发布

蓝星福

最新推荐文章于 2024-08-15 09:31:54 发布

阅读量8.5k

点赞数 1

分类专栏： MySQL

本文链接：https://blog.csdn.net/bluestarf/article/details/46622455

版权

MySQL 专栏收录该内容

20 篇文章 0 订阅

订阅专栏

翻译：MySQL不能指定更新的目标表在FROM子句

源SQL语句：

delete from t_official_sys_user
where USER_NAME IN(SELECT USER_NAME FROM t_official_sys_user b group by b.`USER_NAME` having  count(1) > 1)

执行报以下错误：

[SQL] 

delete from t_official_sys_user
where USER_NAME IN(SELECT USER_NAME FROM t_official_sys_user b group by b.`USER_NAME` having  count(1) > 1)

[Err] 1093 - You can't specify target table 't_official_sys_user' for update in FROM clause

解决代码如下：

delete from t_official_sys_user
where USER_NAME IN(select USER_NAME from ( SELECT USER_NAME FROM t_official_sys_user b group by b.`USER_NAME` having  count(1) > 1) as temtable );

分析：

先把要删除的目标放到一张临时表再把要删除的条件指定到这张临时表即可。