数据库批量更新

使用update-set-values-where可以更新多条记录，但更新值是相同的。

使用update-set-from-where更新多条记录，更新值可以定制不同。

用法

update target
set target.c1=src.c1,...,target.cn=src.cn
from
(
select --准备好数据
) src
where target.p1=src.p1 --按target的p1更新

例子

update t_exportChargeMistakeRatio
set longMoney=lm,shortMoney=sm,mistakeTimes=mt,mistakePersons=mp
from(
	select station,sum(longerAmount)  lm , sum(shorterAmount) sm,count(shorterAmount) mt,count (distinct(jobNumber)) mp
	from t_importLongShortMoney
	where recordDate between '2009-11-1' and '2009-11-30'			 
	group by t_importLongShortMoney.station
)a
where t_exportChargeMistakeRatio.stationId=a.station

update t_exportChargeMistakeRatio
set chargeMistakeRatio=cmr
from (select stationId,(isnull(longMoney,0)+isnull(shortMoney,0))/receivableCash  cmr
	from t_exportChargeMistakeRatio
	where date='2009-11-1'
) a
where a.stationId = t_exportChargeMistakeRatio.stationId