How to update a column fetched by a cursor in TSQL

Question

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

Answer 1

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.

Answer 2

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