update多条Select出来的数据.doc

《update多条Select出来的数据.doc》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

1、update多条Select出来的数据好多朋友喜欢用游标解决此问题，但是执行速度狂慢！其实解决起来很简单了：先来个简单的：把FLowER的Am_employee表的email,dept_id,ext_no多条数据按emp_no对应update到EmpBaseInfo表中：updateEmpBaseInfosetemail=b.Mail_account,dept_id=b.dept_code,ext_no=b.ext_nofromflower.flower.dbo.am_employeebwhereb.emp_no=Em

2、pBaseInfo.emp_no复杂一点的：多表select，并将结果对应插入到指定的表中：updateEmpBaseInfosetJob_Rank=b.level_numberfromflower.flower.dbo.am_employeea,flower.flower.dbo.am_levelb,flower.flower.dbo.am_approver_levelcwherea.emp_id=c.emp_idandb.level_number=c.level_numberanda.emp_no=EmpBaseI

3、nfo.emp_no取别名的：updatexsetJob_Rank=b.level_numberfromflower.flower.dbo.am_employeea,flower.flower.dbo.am_levelb,flower.flower.dbo.am_approver_levelc,EmpBaseInfoxwherea.emp_id=c.emp_idandb.level_number=c.level_numberanda.emp_no=x.emp_no--另外一种方式:临时表ViewCode偶用临时表来解决

4、1、创建临时表#temp_1SELECTcard_id,sum(card_cost)as'sum_cost'INTO#tmp_1FROMpos_t_vip_costGROUPbycard_idgo2、更新表pos_t_vip_info记录UPDATEpos_t_vip_info,#tmp_1SETpos_t_vip_info.card_amount=#tmp_1.sum_costWHEREpos_t_vip_info.card_id=#tmp_1.card_idgo3、删除临时表DROPTABLE#tmp_1go 以上

5、是参考资料 ViewCodeupdate[StormPlatform120316].[dbo].[t_Assessment]setDevelopers=le.DevelopmentPrincipalfrom[StormPlatform120316].[dbo].[t_Assessment]aleftjoin[StormPlatform0623].[dbo].[t_LeaderEvaluation]leona.RequirementApplyID=le.RequirementApplyIDandle.createtime

6、in(selectmax(createtime)from[StormPlatform0623].[dbo].[t_LeaderEvaluation]groupbyRequirementApplyID)wherea.Developers=''Mysql的update语句ViewCode2010-04-2420:31mysqlupdatefrom子查询mssql子查询更新updatelogsetuin=b.uinfromloga,logsbwherea.accountuin=b.accountuinmysql不支持upda

7、te子查询更新找了半天资料终于搞定了...update`log`ainnerjoin`logs`bona.`accountuin`=b.`accountuin`seta.`uin`=b.`uin`wherea.`accountuin`=b.`accountuin`这样就能将查询出来的字段批量更新过去了...