I am trying to update a row inside a cursor. What I am trying to do is update a chain of records with OLD_QTY and NEW_QTY. However when I try to do my update it gives the error The cursor is READ ONLY
even though I included for update of OLD_QTY, NEW_QTY
in my declration. It makes no difference if I include OLD_QTY
and NEW_QTY
in the select statement.
declare @current_inv_guid uniqueidentifier
declare @last_inv_guid uniqueidentifier
declare @current_vid int
declare @last_vid int
--declare @current_new_qty money
declare @last_new_qty money
--declare @current_old_qty money
declare iaCursor cursor
for select INV_GUID, old_VID
--, OLD_QTY, NEW_QTY
from #IA
order by INV_GUID, old_vid, ENTRY_NUM
for update --of OLD_QTY, NEW_QTY
open iaCursor
Fetch next from iaCursor into @current_inv_guid, @current_vid --, @current_old_qty, @current_new_qty
while @@fetch_status = 0
begin
--test to see if we hit a new chain.
if(@last_inv_guid <> @current_inv_guid or @current_vid <> @last_vid)
begin
set @last_new_QTY = (select #lots.QTY_RECEIVED from #lots where #lots.INV_GUID = @current_inv_guid and LOT_VID = @current_vid)
set @last_inv_guid = @current_inv_guid
set @last_vid = @current_vid
end
--update the current link in the chain
update #ia
set OLD_QTY = @last_new_QTY,
NEW_QTY = @last_new_QTY + QTY_CHANGE,
@last_new_QTY = @last_new_QTY + QTY_CHANGE
where current of iaCursor
--get the next link
fetch next from iaCursor into @current_inv_guid, @current_vid --, @current_old_qty, @current_new_qty
end
close iaCursor
deallocate iaCursor
Putting a order by
in the select
made the cursor read only.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With