title: How to update a column fetched by a cursor in TSQL
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>In the example below, I'm trying to update the field CurrentPOs in temporary table #t1, based upon a query that uses <code>#t1.Product_ID</code> to look up the required value. You will see in the code that I have attempted to use the notation <code>curPO.Product_ID</code> to reference this, but it doesn't work. I have also attempted to use an update statement against curPO, also unsuccessfully.</p>
  
  <p>I can make the code work by fetching to variables, but I'd like to know how to update the field directly.</p>
  
  <p>I think I'm probably missing something obvious, but can anyone help?</p>
  
  <pre><code>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 = &lt;calculation&gt;,
                  ReceiveQuantity = &lt;calculation&gt;
      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
  </code></pre>