真的是学得够烂,今天才知道这个知识点:mysql update不能直接使用select的结果
在sql server,oracle中,我们可是使用以下update语句对表进行更新:
update a set a.xx= (select yy from b) ;
但是在mysql中,不能直接使用set select的结果,必须使用inner join:
update a inner join (select yy from b) c set a.xx = c.yy
例子: update virtualmachine v INNER JOIN (select vm.id from virtualmachine vm left join functionalci fi on vm.id=fi.id where locate('.',fi.name)<=0 ) c on v.id=c.id set v.category='VirtualDesktop';
update ( select dataresource
from KDTA_TA1130.ecpay_abnormalrcd
where businesscode = '98'
and dataresource = '0'
and ecno ='00682'
and abnormallevel = '1' ) t
set t.dataresource =9