SQL Update 巧用

JOIN 样本 **********************************

Update 结存 set 结存.现有库存=c.入仓数-b.出仓数量
from 结存 a
join (select 物料代码,sum(isnull(入仓数,0)) 入仓数 from 进仓 group by 物料代码) c 
on a.代码=c.物料代码
left join (select 代码,sum(isnull(出仓数量,0)) 出仓数量 from 领料 group by 代码) b 
on a.代码=b.代码

 

 1. 方法  （Join 方式 Update ) 推荐 ...

Update hekTapMain
  SET dtCount=TapDetail.cnt_tdID
FROM hekTapMain TapMain
join 
   (SELECT htMainID, Count(tdID) as cnt_tdID FROM hekTapDetail Group by htMainID) TapDetail 
 on TapMain.htID=TapDetail.htMainID
WHERE TapMain.htID=58

 

2.  方法  （Where 方式)

Update hekTapMain
  SET dtCount=TapDetail.cnt_tdID
FROM
  (SELECT Count(tdID) as cnt_tdID
   FROM hekTapDetail WHERE htMainID=58) 
as TapDetail
WHERE hekTapMain.htID=58