MySQL 中 You can‘t specify target table ‘表名‘ for update in FROM clause错误解决办法

Catalina_yep

于 2021-09-26 15:44:41 发布

阅读量932

点赞数

分类专栏： bug解决文章标签： mysql sql

本文链接：https://blog.youkuaiyun.com/Apple_Andy/article/details/120490249

版权

bug解决专栏收录该内容

24 篇文章

订阅专栏

博客讨论了在执行SQL删除操作时遇到的问题，即不能在`DELETE`语句中直接使用子查询。文中通过一个具体的错误示例展示了试图在`WHERE`子句中嵌套子查询的情况，并提供了解决方案，即通过创建中间表来避免子查询。最终的修正后的SQL语句成功地实现了预期的删除操作，确保了`warehouse_code`不为空并过滤了特定角色类型。

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

这里写自定义目录标题

问题解释：不能在更新或删除的sql里嵌套子查询
错误sql:

delete from ent_role_user
     where  (user_code ,role_code, tenant_code )
     in (
        select   user_code ,role_code,tenant_code
        from ent_role_user where user_code = #{userName}
        and tenant_code = #{tenantCode}
        and warehouse_code != '' and warehouse_code is not null
        and role_code in (select role_code  from ent_role where role_type =false   ) )
        and warehouse_code != '' and warehouse_code is not null

解决办法：中间加一个中间表

 delete from ent_role_user
     WHERE
	(user_code,role_code,tenant_code) IN (
	SELECT
		a.user_code,a.role_code,a.tenant_code
	FROM(
		SELECT
			user_code,role_code,tenant_code
		FROM
			ent_role_user
		WHERE
			user_code = #{userName}
			AND tenant_code = #{tenantCode}
			AND warehouse_code != ''
			AND warehouse_code IS NOT NULL
			AND role_code IN ( SELECT role_code FROM ent_role WHERE role_type = FALSE )
			AND warehouse_code != ''
			AND warehouse_code IS NOT NULL
		) a
	) and warehouse_code != '' and warehouse_code is not null