从UPDATE查询

Question

获得更新的值我有一个更新查询：

UPDATE FKMS_GNST_Transaction_Details 
    SET Received_Quantity=Received_Quantity+(
         CASE 
         WHEN (@int_Updated_Qty)>=(GTD.Quantity-GTD.Received_Quantity) 
     THEN GTD.Quantity-GTD.Received_Quantity 
         ELSE (@int_Updated_Qty) 
         END) 
     ,@int_GNST_Reference_Id=GTD.Transaction_Detail_Id 
    FROM FKMS_GNST_Transaction_Details GTD 
INNER JOIN #tbl_transactions tmp 
    ON tmp.Transaction_id=GTD.Transaction_id 
    AND GTD.Item_id=tmp.Item_id 

我想它被添加到Received_Quantity场的数量。也就是说，如果(@int_Updated_Qty)>=(GTD.Quantity-GTD.Received_Quantity) then GTD.Quantity-GTD.Received_Quantity其他明智的@int_Updated_Qty。

我们怎样才能把这个值（变成一个变量或任何其他方式）？请帮忙。

Answer 1

使用OUTPUT clause

UPDATE FKMS_GNST_Transaction_Details 
    SET Received_Quantity=Received_Quantity+(
         CASE 
         WHEN (@int_Updated_Qty)>=(GTD.Quantity-GTD.Received_Quantity) 
     THEN GTD.Quantity-GTD.Received_Quantity 
         ELSE (@int_Updated_Qty) 
         END) 
     ,@int_GNST_Reference_Id=GTD.Transaction_Detail_Id 

--start gbn code 
OUTPUT INSERTED.Received_Quantity 
--end gbn code 

    FROM FKMS_GNST_Transaction_Details GTD 
INNER JOIN #tbl_transactions tmp 
    ON tmp.Transaction_id=GTD.Transaction_id 
    AND GTD.Item_id=tmp.Item_id 

输出结果可以去

• 为表（真实的，临时或变量）
• 到客户端作为一个记录

您不能直接分配到本地变量