mysql Code: 1093. You can't specify target table for update in FROM clause

执行如下sql会报错，大概是delete的where条件里面不能包含自身的表

delete from t_plan_new where plan2code is not null and plan2version is not null and (plan2code,plan2version) not in(select plan2code,max(plan2version) from t_plan_new group by plan2code)

 

所以用临时表

CREATE TEMPORARY TABLE IF NOT EXISTS temp_table AS
select plan2code,max(plan2version) from t_plan_new group by plan2code

然后

delete from t_plan_new where plan2code is not null and plan2version is not null and (plan2code,plan2version) not in(   select * from temp_table)

但是又发现一个问题

因为select * from temp_table里面有null所以用not in会一条都匹配不到

所以要么改成not exists取代not in

要么在not in的子查询里面加入非null判断

delete from t_plan_new where plan2code is not null and plan2version is not null and (plan2code,plan2version) not in(   select * from temp_table  where plan2code is not null)