問題描述

在我繼續(xù)之前:是的，我知道與基于集合的操作相比，游標的性能很差.在這種特殊情況下，我在一個包含 100 條左右記錄的臨時表上運行游標，并且該臨時表總是相當(dāng)小，因此性能不如靈活性重要.

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.

我的困難在于我無法找到如何更新由游標獲取的列的示例.以前，當(dāng)我使用游標時，我將值檢索到變量中，然后根據(jù)這些值在每一步運行更新查詢.這次我想更新臨時表中的一個字段，但我不知道該怎么做.

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.

在下面的示例中，我嘗試根據(jù)使用 #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.

這篇關(guān)于如何更新 TSQL 中游標獲取的列的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！