CODEWITHSUNDEEP
sqlsql-servert-sql
Billious
Billious

Reputation: 2583

How to update a column fetched by a cursor in TSQL

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.

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

Upvotes: 7

Views: 74785

Answers (4)

Joel R. Hall
Joel R. Hall

Reputation: 167

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.

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

Upvotes: 0

Pavel Kovalev
Pavel Kovalev

Reputation: 8116

Maybe you need something like that:

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

Upvotes: 2

Billious
Billious

Reputation: 2583

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_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.

Upvotes: 13

Jeremy Stein
Jeremy Stein

Reputation: 19661

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

Upvotes: 3

Related Questions