请教大神一个SQL存储过程循环更新写法的问题

[墨色成画]

各位大神是这样的，打比方我现在有两个表Table_A和Table_B,还有个类型Type(I01或I02或I03，为减)

Table_A Table_B

WHCode ID

BatchNo WorkCode

PCode WHCode

PName PCode

Sum PlanCount

其中表B的 WHCode和PCode 和表A的对应，现在想根据表B的PlanCount数量和Type 去更新表A的Sum数量

比如Sum=1000,PlanCount=500，Type=1

@NewSum=Sum-PlanCount

@NewSum=500

比方现在表B有多行记录：
ID=1，WorkCode=A001,WHCode=C01,PCode=P01,PlanCount=500
ID=2，WorkCode=A001,WHCode=C01,PCode=P02,PlanCount=200
表A
PCode=P01,Sum=1000
PCode=P02,Sum=800
如何将表A的WHCode=C01，PCode=P01,P02的Sum 分别更新成500和600啊？

下面是我写的代码：

if(@Type='I01' or @Type='I02' or @Type='I03')
begin

select @ProductCode=ProductCode,@PlanCount=PlanCount,@WareHouseCode=WareHouseCode
,@StoreCellICode=StoreCellICode
from WH_OutBillDetail where OutBillCode=@WorkCode

select @RealQty=RealQty from WH_StockTotalQty where WareHouseCode=@WareHouseCode and ProductCode=@ProductCode

set @NewRealQty=@RealQty-@PlanCount

declare UpdateSumCursor cursor --声明一个游标，查询满足条件的数据
for select ProductCode,PlanCount,WareHouseCode
,StoreCellICode from WH_OutBillDetail（等同表B） where OutBillCode=@WorkCode

open UpdateSumCursor--打开

declare @noToUpdate varchar(20)
fetch next from UpdateSumCursor into @noToUpdate--声明一个变量，用于读取游标中的值

while @@fetch_status=0 --循环读取
begin

update WH_StockTotalQty（等同表A） set RealQty=@NewRealQty where WareHouseCode=@WareHouseCode and
ProductCode=@ProductCode
fetch next from UpdateSumCursor into @noToUpdate
end

close UpdateSumCursor --关闭

deallocate UpdateSumCursor --删除

end

我感觉应该查询的时候也循环分别赋值2个数量500和200，但有点晕，第一次用存储过程，小白望大神指导下，万分感谢！

[中国风]

【SQL Server版块】提问的智慧 http://bbs.csdn.net/topics/391996442

[中国风]

看看是不是这样的效果

if(@Type='I01' or @Type='I02' or @Type='I03')
begin
UPDATE b
SET RealQty=b.RealQty-a.PlanCount
FROM (SELECT ProductCode,WareHouseCode,SUM(PlanCount) AS PlanCount FROM WH_OutBillDetail WHERE OutBillCode=@WorkCode GROUP BY ProductCode,WareHouseCode) AS a 
	INNER JOIN WH_StockTotalQty AS b ON b.ProductCode=a.ProductCode AND b.WareHouseCode=a.WareHouseCode    
END

[qq_27559503]

if(@Type='I01' or @Type='I02' or @Type='I03')
	begin
	
    declare UpdateSumCursor cursor 
    for select  ProductCode,PlanCount,WareHouseCode
    ,StoreCellICode	from WH_OutBillDetail where OutBillCode=@WorkCode
    	
	 open UpdateSumCursor
	 
	 declare @Pcode varchar(50), @Plan int, @WHCode varchar(50) , @Scode varchar(50)   
        fetch next from UpdateSumCursor into @Pcode,@Plan,@WHCode,@Scode 
		
	 while @@fetch_status=0  
        begin
        select @RealQty=RealQty from WH_StockTotalQty where WareHouseCode=@WHCode and ProductCode=@Pcode
	    set @NewRealQty=@RealQty-@Plan
       update WH_StockTotalQty set RealQty=@NewRealQty where WareHouseCode=@WHCode and ProductCode=@Pcode
        fetch next from UpdateSumCursor into  @Pcode,@Plan,@WHCode,@Scode 
        end
    
    close UpdateSumCursor
    
    deallocate UpdateSumCursor  
    
end

select  @ProductCode=ProductCode,@PlanCount=PlanCount,@WareHouseCode=WareHouseCode
    ,@StoreCellICode=StoreCellICode
from WH_OutBillDetail where OutBillCode=@WorkCode---删掉

select @RealQty=RealQty from WH_StockTotalQty where WareHouseCode=@WareHouseCode and ProductCode=@ProductCode
set @NewRealQty=@RealQty-@PlanCount 放到下面循环里

把你声明的那些列都赋值给变量不就行了。然后循环查询更新。

[墨色成画]

谢谢版主大大和2楼的大神，应该都可以，谢谢，万分感谢！