MYSQL之You can't specify target table for update in FROM clause解决办法

最新推荐文章于 2024-01-03 11:14:48 发布

pri_sta_pub

最新推荐文章于 2024-01-03 11:14:48 发布

阅读量599

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/pri_sta_pub/article/details/80609580

本文解决了在执行复杂SQL删除语句时遇到的错误：Youcan'tspecifytargettableforupdateinFROMclause。通过调整子查询的方式，最终实现了正确执行。

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

更新或删除时报错 You can't specify target table for update in FROM clause

例如:

DELETE FROM cm_object WHERE id IN(SELECT id FROM cm_object WHERE address_id IN(SELECT id FROM cm_address WHERE building_id IN(SELECT id FROM cm_building WHERE building_name LIKE '图书馆%')));

报错

修改如下:

DELETE FROM cm_object WHERE id IN(SELECT a.id FROM (SELECT id FROM cm_object WHERE address_id IN(SELECT id FROM cm_address WHERE building_id IN(SELECT id FROM cm_building WHERE building_name LIKE '图书馆%'))) a);

执行成功!