简记Mysql报You can't specify target table 't_food_material' for update in FROM clause错误的解决办法

原sql

DELETE  from t_food_material where id in (select max(id) from t_food_material group by food_material_name  having count(*)>1)  and two_level_classify_name='成品食物' order by id

执行结果:

解决方案:
将SELECT查询的结果,通过中间表再次SELECT一遍，这样就规避了错误。改造后的sql如下:

 

DELETE from t_food_material  where id in (select a.id FROM (select max(id) as id from  t_food_material  group by food_material_name  having count(*)>1) a) and two_level_classify_name='xxx'