Billious Billious - 1 month ago 5
SQL Question

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

Answer

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.