从Select语句的总和更新值

Question

@dt TVP_API_DELIVERY_ORDER  READONLY    
    UPDATE  Stkm SET
            Stkm.Itm_Qty_OnHand = CASE 
                                    WHEN tvp.Flag='I' THEN (Itm_Qty_OnHand- (SELECT SUM(T1.OrderQty) FROM @dt T1 WHERE T1.ItemCode=tvp.ItemCode)) 
                                    WHEN tvp.Flag='U' THEN ((Itm_Qty_OnHand+(SELECT SUM(T2.Dod_Qty) FROM DoD T2 WHERE T2.Itm_cd=tvp.ItemCode AND T2.Do_No=@OrderNo AND T2.Do_Year=@Year))-(SELECT SUM(T3.OrderQty) FROM @dt T3 WHERE T3.ItemCode=tvp.ItemCode))
                                    WHEN tvp.Flag='D' THEN (Itm_Qty_OnHand + (SELECT SUM(T4.Dod_Qty) FROM DoD T4 WHERE T4.Itm_cd=tvp.ItemCode AND T4.Do_No=@OrderNo AND T4.Do_Year=@Year))                                          
                                    ELSE 0
                                  END       
    FROM     @dt tvp 
    INNER JOIN Stkm ON tvp.ItemCode=Stkm.Itm_Cd

如何简化上述查询？有没有办法让这个查询的执行速度更快呢？我正在将值从表值参数(TVP)更新到表中。

Answer 1

 Try this way this would be helpful to you:
 Also if found any difficulty let me know. 

 @dt TVP_API_DELIVERY_ORDER  READONLY    
                    UPDATE  Stkm SET
                            Stkm.Itm_Qty_OnHand = CASE 
                                                    WHEN tvp.Flag='I' THEN (Itm_Qty_OnHand- TmporderQunatity.orderQuantity) 
                                                    WHEN tvp.Flag='U' THEN ((Itm_Qty_OnHand+TmpDodQunatity.DodQuantity)-TmporderQunatity.orderQuantity)
                                                    WHEN tvp.Flag='D' THEN (Itm_Qty_OnHand + TmpDodQunatity.DodQuantity)                                          
                                                    ELSE 0
                                                  END       
                    FROM     @dt tvp 
                    INNER JOIN Stkm ON tvp.ItemCode=Stkm.Itm_Cd
                    CROSS APPLY (
                        SELECT SUM(T1.OrderQty) AS orderQuantity FROM @dt T1 
                        WHERE T1.ItemCode=tvp.ItemCode
                    )AS TmporderQunatity
                    CROSS APPLY (
                        SELECT SUM(T2.Dod_Qty) AS DodQuantity FROM @dt T2
                        WHERE T2.ItemCode=tvp.ItemCode AND T2.Do_No=@OrderNo AND T2.Do_Year=@Year
                    )AS TmpDodQunatity