Cursor says its read only even though I declared it "for update"

Question

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

related : http://stackoverflow.com/questions/14172501/why-an-cursor-opened-for-a-select-with-order-by-does-not-reflect-updates-to-the — bummi, Oct 12 '13 at 07:21

score 11 · Answer 1 · answered Dec 12 '11 at 21:16

11

Putting a order by in the select made the cursor read only.

answered Dec 12 '11 at 21:16

Scott Chamberlain

124,994
33
282
431

This answer is a bit misleading, so I posted my own. – GSerg Dec 12 '11 at 21:41
1

Apparently, it will also flag it as read_only if there is a SELECT DISTINCT. – rmcmullan Dec 10 '19 at 18:58
1

Or SELECT TOP(50) – JSmart523 Sep 25 '20 at 23:51

score 9 · Answer 2 · answered Dec 12 '11 at 21:40

You are not explicitly saying what behaviour you want, therefore, default rules apply, according to which, the cursor may or may not be updatable, depending on the underlying query.

It's perfectly fine to use order by in an updatable cursor, but you have to be more verbose and tell SQL Server what you want in details, for instance:

declare iaCursor cursor
local
forward_only
keyset
scroll_locks
for
  select INV_GUID, old_VID
  from #IA 
  order by INV_GUID, old_vid, ENTRY_NUM 
for update of OLD_QTY, NEW_QTY

score 6 · Answer 3 · edited Jun 20 '20 at 09:12

6

There's an import but subtle note on the documentation page that Patrick listed:

If the query references at least one table without a unique index, the keyset cursor is converted to a static cursor.

And of course STATIC cursors are read-only.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jul 26 '16 at 16:35

Karl Hoaglund

603
10
10

Clockwork-Muse · Accepted Answer · 2014-09-03T10:23:15.697

Besides the reason you mentioned in your answer, what you're attmepting to do runs counter to the way SQL is meant to be used. Try to update the data in sets, not by rows.

I'm not positive, as I don't know your table design, but I believe the following should work. You may get better performance out of this. In particular, I'm assuming that QTY_CHANGE is coming from #ia, although this may not be the case.

UPDATE #ia as a set (OLD_QTY, NEW_QTY) = (SELECT #lots.QTY_RECEIVED + (COUNT(b.*) * a.QTY_CHANGE), 
                                                 #lots.QTY_RECEIVED + ((COUNT(b.*) + 1) * a.QTY_CHANGE)
                                          FROM #lots
                                          LEFT JOIN #ia as b
                                          ON b.INV_GUID = a.INV_GUID
                                          AND b.OLD_VID = a.OLD_VID
                                          AND b.ENTRY_NUM < a.ENTRY_NUM
                                          WHERE #lots.INV_GUID = a.INV_GUID
                                          AND #lots.LOT_VID = a.OLD_VID)
WHERE EXISTS (SELECT '1'
              FROM #lots
              WHERE #lots.INV_GUID = a.INV_GUID
              AND #lots.LOT_VID = a.OLD_VID)

EDIT:

... the previous version of the answer was written with a DB2 perspective, although it would otherwise be db-agnostic. It also had the problem of using the same value of QTY_CHANGE for every row, which is unlikely. This should be a more idiomatic SQL Server 2008 version, as well as being more likely to output the correct answer:

WITH RT AS (SELECT #IA.inv_guid, #IA.old_vid, #IA.entry_num,
                   COALESCE(MAX(#Lots.qty_received), 0) + 
                         SUM(#IA.qty_change) OVER(PARTITION BY #IA.inv_guid, #IA.old_vid 
                                                  ORDER BY #IA.entry_num) 
                        AS running_total                                      
                       FROM #IA
                       LEFT JOIN #Lots
                              ON #Lots.inv_guid = #IA.inv_guid
                                 AND #Lots.lot_vid = #IA.old_vid)
UPDATE #IA
SET #IA.old_qty = RT.running_total - #IA.qty_change, #IA.new_qty = RT.running_total
FROM #IA
JOIN RT
  ON RT.inv_guid = #IA.inv_guid
     AND RT.old_vid = #IA.old_vid
     AND RT.entry_num = #IA.entry_num

Patrick · Answer 5 · 2014-09-03T12:30:33.213

0

Some cursor declarations do not allow updates. The documentation gives a hint in the following remark:

If the SELECT statement does not support updates (insufficient permissions, accessing remote tables that do not support updates, and so on), the cursor is READ_ONLY.

I ran into the same issue when trying to join the "inserted" object of a trigger in the select statement of the cursor declaration.

edited Sep 03 '14 at 12:30

answered Sep 02 '14 at 15:16

Patrick

668
4
11

score 0 · Answer 6 · answered Jun 12 '18 at 13:47

0

Use the DYNAMIC clause, found in documentation.

Defines a cursor that reflects all data changes made to the rows in its result set as you scroll around the cursor. The data values, order, and membership of the rows can change on each fetch.

answered Jun 12 '18 at 13:47

SNR

712
1
8
22

Cursor says its read only even though I declared it "for update"

6 Answers6

EDIT: