久久久久久久av_日韩在线中文_看一级毛片视频_日本精品二区_成人深夜福利视频_武道仙尊动漫在线观看

如何更新 TSQL 中游標獲取的列

How to update a column fetched by a cursor in TSQL(如何更新 TSQL 中游標獲取的列)
本文介紹了如何更新 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 中游標獲取的列的文章就介紹到這了,希望我們推薦的答案對大家有所幫助,也希望大家多多支持html5模板網!

【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題,如果有圖片或者內容侵犯了您的權益,請聯系我們刪除處理,感謝您的支持!

相關文檔推薦

Converting Every Child Tags in to a Single Column with multiple Delimiters -SQL Server (3)(將每個子標記轉換為具有多個分隔符的單列-SQL Server (3))
How can I create a view from more than one table?(如何從多個表創建視圖?)
Create calculated value based on calculated value inside previous row(根據前一行內的計算值創建計算值)
How do I stack the first two columns of a table into a single column, but also pair third column with the first column only?(如何將表格的前兩列堆疊成一列,但也僅將第三列與第一列配對?) - IT屋-程序員軟件開發技
Recursive t-sql query(遞歸 t-sql 查詢)
Convert Month Name to Date / Month Number (Combinations of Questions amp; Answers)(將月份名稱轉換為日期/月份編號(問題和答案的組合))
主站蜘蛛池模板: 日韩黄色一级片 | 又色又爽又黄gif动态图 | 国产高清视频一区 | 国产午夜三级 | 日日操夜夜爽 | 国产色网站 | 91在线小视频 | 国产午夜免费视频 | 日韩一级大片 | 国产色视频一区二区三区qq号 | 欧美一区二区三区成人 | 99精品久久久久久 | 色综合国产 | 午夜精品视频在线观看 | 国产伦理一区二区 | wwwav在线 | 久久精品免费观看 | 97青青草 | 国产精品美女在线观看 | 日韩成人在线观看视频 | 午夜黄色影院 | 天天爱天天操 | 亚洲免费毛片 | 黄色成人小视频 | 亚洲成a | 一级黄视频 | 日韩欧美自拍 | 中文字幕国产视频 | 国产色网站| 国产美女毛片 | 国产寡妇亲子伦一区二区三区四区 | 91中文在线| 天天爽天天 | 性视频在线 | 免费毛片观看 | 亚洲一区二区三区四区在线 | 中文在线免费观看 | 精品久久影院 | 综合99| 性大毛片视频 | 欧美精品二区三区四区免费看视频 |