首页 > 代码库 > (原)mysql错误1093 You can't specify target table 'wms_cabinet_form' for update in FROM clause

(原)mysql错误1093 You can't specify target table 'wms_cabinet_form' for update in FROM clause

这个错误的意思是不能先select出同一表中的某些值,再update这个表(在同一语句中),解决方法不直接查询同一个,假设要更新的表为A,则先将A的的数据放到表B,再从表B中查询则得到更新和查询表A的效果

例:

成绩表:                       

把“SC”表中“姚明”老师教的课的成绩都更改为此课程的平均成绩

update sc set score=(select avg(a.score) FROM (select score,C from sc) a where a.C in (select C from course left join teacher on course.T=teacher.T where Tname=‘姚明‘) group by a.C) where C in (select C from course left join teacher on course.T=teacher.T where Tname=‘姚明‘)

 

其中红色字体就将sc的数据放到一个临时表a中,再查询表a

(原)mysql错误1093 You can't specify target table 'wms_cabinet_form' for update in FROM clause