SQL 常用UPDATE语句

update 表A set 字段=b.字段
from 表A as a,  表B as b
where a.字段=b.字段

 

例:

update #project_statistics set SPI = b.SPI
 from #project_statistics a,(
 select bb.name as departmentName,cc.name as projectType,sum(aa.plan_finish_scale/(aa.project_use_day/DATEDIFF(day,aa.finish_date_time,aa.start_date)))/count(aa.id) SPI
 from project_weekly aa
 left join code_mst bb on bb.third_class='PROJECTWEEKLY_IMPLEMENTDEPARTMENT' and bb.value=aa.implement_department
 left join code_mst cc on cc.third_class='PROJECTWEEKLY_TYPE'and cc.parent_id= bb.id
 group by bb.name,cc.name
 )b
 where a.department=b.departmentName and a.project =b.projectType