如何更新 TSQL 中游标获取的列 [英] How to update a column fetched by a cursor in TSQL

问题描述

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

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.

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

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.

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

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

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

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

推荐答案

在进行了更多的谷歌搜索后，我找到了部分解决方案.更新代码如下:

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 

我仍然需要使用 FETCH INTO 来检索 #t1.Product_ID 并运行生成 #PO 的查询，所以我仍然想知道如果可以单独使用 FETCH.

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

这篇关于如何更新 TSQL 中游标获取的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！