sql ： You can't specify target table '表名' for update in FROM clause

解决MySQL更新时的You can't specify target table错误

最新推荐文章于 2024-12-27 09:43:29 发布

原创最新推荐文章于 2024-12-27 09:43:29 发布 · 672 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#You can't specify target table

代码常用知识专栏收录该内容

31 篇文章

订阅专栏

在尝试删除包含外键约束的MySQL数据时遇到'You can't specify target table for update in FROM clause'错误。解决方法是先查询@@FOREIGN_KEY_CHECKS以了解外键检查状态，然后设置FOREIGN_KEY_CHECKS为0关闭外键检查，执行删除操作，最后恢复外键检查设置为1。

bus_customer是主表。bus_customer_info_student是副表。bus_customer_info_student表中存了外键

刚开始查询错误数据的时候是正常的

SELECT istu.* FROM `bus_customer` c
left join bus_customer_info_student istu on c.id = istu.customer_id
 where c.is_del =0 and c.customer_type_id = 1
and istu.id is null

想删除的时候就提示错误了：

delete from bus_customer where id in(
(SELECT c.id id FROM `bus_customer` c
left join bus_customer_info_student istu on c.id = istu.customer_id
 where c.is_del =0 and c.customer_type_id = 1
and istu.id is null)

You can't specify target table for update in FROM clause。意思是说，不能先select出同一表中的某些值，再update这个表(在同一语句中)改成酱婶儿的就好了

delete from bus_customer where id in(
SELECT a.id from
(SELECT c.id id FROM `bus_customer` c
left join bus_customer_info_student istu on c.id = istu.customer_id
 where c.is_del =0 and c.customer_type_id = 1
and istu.id is null) a)

但是在删的时候还会提示：有外键不能删除的提示，那么就用下面的sql改一下

SELECT @@FOREIGN_KEY_CHECKS; -- 查询外键状态

SET FOREIGN_KEY_CHECKS = 0;-- 设置变量，关闭唯一性校验

然后就可以删除了。删完再把变量还原回去

SET FOREIGN_KEY_CHECKS=1;