SQL 如何更新 TSQL 中游标获取的列

How to update a column fetched by a cursor in TSQL

提问by Billious

Before I go any further: Yes, I know that cursors perform poorly compared with set-based operations. In this particular case I'm running a cursor on a temporary table of 100 or so records, and that temporary table will always be fairly small, so performance is less crucial than flexibility.

在我继续之前：是的，我知道与基于集合的操作相比，游标的性能很差。在这种特殊情况下，我在一个包含 100 条左右记录的临时表上运行游标，并且该临时表总是相当小，因此性能不如灵活性重要。

My difficulty is that I'm having trouble finding an example of how to update a column fetched by a cursor. Previously when I've used cursors I've retrieved values into variables, then run an update query at each step based upon these values. On this occasion I want to update a field in the temporary table, yet I can't figure out how to do it.

我的困难在于我无法找到如何更新由游标获取的列的示例。以前，当我使用游标时，我将值检索到变量中，然后根据这些值在每一步运行更新查询。在这种情况下，我想更新临时表中的一个字段，但我不知道该怎么做。

In the example below, I'm trying to update the field CurrentPOs in temporary table #t1, based upon a query that uses #t1.Product_IDto look up the required value. You will see in the code that I have attempted to use the notation curPO.Product_IDto reference this, but it doesn't work. I have also attempted to use an update statement against curPO, also unsuccessfully.

在下面的示例中，我尝试根据#t1.Product_ID用于查找所需值的查询来更新临时表 #t1 中的字段 CurrentPOs 。您将在代码中看到我尝试使用该符号curPO.Product_ID来引用它，但它不起作用。我还尝试对 curPO 使用更新语句，但也未成功。

I can make the code work by fetching to variables, but I'd like to know how to update the field directly.

我可以通过获取变量来使代码工作，但我想知道如何直接更新该字段。

I think I'm probably missing something obvious, but can anyone help?

我想我可能遗漏了一些明显的东西，但有人可以帮忙吗？

declare curPO cursor
for select Product_ID, CurrentPOs from #t1
for update of CurrentPOs
open curPO

fetch next from curPO

while @@fetch_status = 0
begin
    select      OrderQuantity = <calculation>,
                ReceiveQuantity = <calculation>
    into        #POs
    from        PurchaseOrderLine POL 
    inner join  SupplierAddress SA ON POL.Supplier_ID = SA.Supplier_ID
    inner join  PurchaseOrderHeader POH ON POH.PurchaseOrder_ID = POL.PurchaseOrder_ID
    where       Product_ID = curPO.Product_ID
    and         SA.AddressType = '1801'

    update curPO set CurrentPOs = (select sum(OrderQuantity) - sum(ReceiveQuantity) from #POs)

    drop table #POs

    fetch next from curPO
end

close curPO
deallocate curPO

回答by Billious

After doing a bit more googling, I found a partial solution. The update code is as follows:

在做了更多的谷歌搜索之后，我找到了一个部分解决方案。更新代码如下：

UPDATE #T1 
SET    CURRENTPOS = (SELECT SUM(ORDERQUANTITY) - SUM(RECEIVEQUANTITY) 
                     FROM   #POS) 
WHERE  CURRENT OF CURPO 

I still had to use FETCH INTO, however, to retrieve #t1.Product_IDand run the query that produces #POs, so I'd still like to know if it's possible to use FETCHon it's own.

FETCH INTO但是，我仍然必须使用来检索#t1.Product_ID和运行生成#PO 的查询，所以我仍然想知道是否可以单独使用FETCH它。

回答by Jeremy Stein

Is this what you want?

这是你想要的吗？

declare curPO cursor
for select Product_ID, CurrentPOs from #t1
for update of CurrentPOs
open curPO

fetch next from curPO

while @@fetch_status = 0
begin
    update curPO set CurrentPOs =
      (select      sum(<OrderQuantityCalculation>)
       from        PurchaseOrderLine POL 
       inner join  SupplierAddress SA ON POL.Supplier_ID = SA.Supplier_ID
       inner join  PurchaseOrderHeader POH ON POH.PurchaseOrder_ID = POL.PurchaseOrder_ID
       where       Product_ID = curPO.Product_ID
       and         SA.AddressType = '1801') -
      (select      sum(<ReceiveQuantityCalculation>)
       from        PurchaseOrderLine POL 
       inner join  SupplierAddress SA ON POL.Supplier_ID = SA.Supplier_ID
       inner join  PurchaseOrderHeader POH ON POH.PurchaseOrder_ID = POL.PurchaseOrder_ID
       where       Product_ID = curPO.Product_ID
       and         SA.AddressType = '1801')

    fetch next from curPO
end

close curPO
deallocate curPO

回答by Pavel Kovalev

Maybe you need something like that:

也许你需要这样的东西：

update DataBaseName..TableName
set ColumnName = value
where current of your_cursor_name;

回答by Joel R. Hall

Here's an example to calculate one column based upon values from two others (note, this could be done during the original table select). This example can be copy / pasted into an SSMS query window to be run without the need for any editing.

这是一个基于其他两个列的值计算一列的示例（注意，这可以在原始表选择期间完成）。此示例可以复制/粘贴到 SSMS 查询窗口中，无需任何编辑即可运行。

DECLARE @cust_id INT = 2, @dynamic_val NVARCHAR(40), @val_a INT, @val_b INT

DECLARE @tbl_invoice table(Cust_ID INT, Cust_Fees INT, Cust_Tax INT)

INSERT @tbl_invoice ( Cust_ID, Cust_Fees, Cust_Tax ) SELECT 1, 111, 11
INSERT @tbl_invoice ( Cust_ID, Cust_Fees, Cust_Tax ) SELECT 2, 222, 22
INSERT @tbl_invoice ( Cust_ID, Cust_Fees, Cust_Tax ) SELECT 3, 333, 33

DECLARE @TblCust TABLE
(
    Rec_ID INT
    , Val_A INT
    , Val_B INT
    , Dynamic_Val NVARCHAR(40)
    , PRIMARY KEY NONCLUSTERED (Rec_ID)
)

INSERT @TblCust(Rec_ID, Val_A, Val_B, Dynamic_Val)
SELECT Rec_ID = Cust_ID, Val_A = Cust_Fees, Val_B = Cust_Tax, NULL
FROM @tbl_invoice

DECLARE cursor_cust CURSOR FOR
    SELECT Rec_ID, Val_A, Val_B, Dynamic_Val
    FROM @TblCust
    WHERE Rec_ID <> @cust_id
    FOR UPDATE OF Dynamic_Val;

OPEN cursor_cust;

FETCH NEXT FROM cursor_cust INTO @cust_id, @val_a, @val_b, @dynamic_val;

WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE @TblCust
        SET Dynamic_Val = N'@c = "' + LTRIM(STR((@val_a + @val_b), 40)) + N'"'
        WHERE CURRENT OF cursor_cust

    FETCH NEXT FROM cursor_cust INTO @cust_id, @val_a, @val_b, @dynamic_val;
END

CLOSE cursor_cust

DEALLOCATE cursor_cust

SELECT * FROM @TblCust