如何更新 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 中游标获取的列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!

相关文档推荐

Creating table with T-SQL - can#39;t see created tables in Object explorer(使用 T-SQL 创建表 - 在对象资源管理器中看不到创建的表)
How to check if VARCHAR strings are (not) hexadecimal?(如何检查 VARCHAR 字符串是否为(非)十六进制?)
Arithmetic overflow error converting IDENTITY to data type int(将 IDENTITY 转换为数据类型 int 的算术溢出错误)
Where clause if there are multiple of the same ID(如果有多个相同的 ID,Where 子句)
Azure SQL: Invalid Object Name using Powershell#39;s quot;Invoke-sqlcmdquot; on Adventureworks(Azure SQL:使用 Powershell 的“Invoke-sqlcmd的无效对象名称在 Adventureworks 上)
How to Convert Table Data into xml format using sql with multiple sub nodes(如何使用具有多个子节点的sql将表数据转换为xml格式)