mysql中delete删除数据，为何如下第一种写法报错，但是第二种就可以了呢？

Question

1、删除表中的数据，第一次写如下：

[SQL]delete  from yizhu_zhixing_plan_expire 
where plan_id in (
select t.plan_id from yizhu_zhixing_plan_expire  t
 where  yizhu_plan_date>=date_format(now() ,'%Y-%m-%d')
and EXISTS (select 1 from yizhu_basic_info a where a.zuhao_id=t.zuhao_id and a.yizhu_State='开始执行' -- and a.type='长期' 
    )
)

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

报错如上：
2、修改脚本后，执行成功了

[SQL]delete  from yizhu_zhixing_plan_expire 
where plan_id in (
select m.plan_id from (
select t.*from yizhu_zhixing_plan_expire  t
 where  yizhu_plan_date>=date_format(now() ,'%Y-%m-%d')
and EXISTS (select 1 from yizhu_basic_info a where a.zuhao_id=t.zuhao_id and a.yizhu_State='开始执行'  
    ) ) m
)

受影响的行: 0
时间: 2.008s

这个是为啥呢？求指导1

Answer 1

MySQL之You can't specify target table for update in FROM clause解决办法_叶落无痕的博客-CSDN博客 这篇文章主要介绍了mysql中You can’t specify target table for update in FROM clause错误解决方法,需要的朋友可以参考下MySQL中You can't specify target table for update in FROM clause错误的意思是说，不能先select出同一表中的某些值，再update这个表(在同一语句中)。 例... https://blog.csdn.net/u012767761/article/details/84997962