Solved

Cursor read-Only Error

Posted on 2011-02-28
3
689 Views
Last Modified: 2012-05-11
     
The following code gives an error of "cursor is read only".
The problem is caused by the order by clause of the Cursor select definition.
Is there a fix for this scenario.
Typically the table will contain records with Batch values of 100,120, 139, 200 ..etc and I wish to
update the Batch values as 10,20,30,40 ..etc  


                declare @n smallint
      declare @batch smallint
      DECLARE cursor1 CURSOR SCROLL_LOCKS
      FOR
      SELECT     Batch
      FROM       JazzCarryOver
      order by Batch FOR UPDATE OF Batch;

      OPEN cursor1;

        set @n = 10
      FETCH NEXT FROM Cursor1 into @batch
-- re number Batch in ascending order
      WHILE @@FETCH_STATUS = 0
      BEGIN
            UPDATE JazzCarryOver
            SET Batch = @n
            WHERE CURRENT OF cursor1;
            set @n = @n + 10
            FETCH NEXT FROM Cursor1 into @lotnum,@batch
    END;
      CLOSE Cursor1;
      DEALLOCATE Cursor1;

0
Comment
Question by:alcindor
3 Comments
 
LVL 12

Assisted Solution

by:HugoHiasl
HugoHiasl earned 100 total points
Comment Utility
updating a row in a cursor where multiple rows are aggregated by using a group by clause is not possible.

What would you expect the update to do? Updating ALL relevant rows or updating only the FIRST or LAST? Or one in the middle?

0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 400 total points
Comment Utility
you dont really need a cursor here , use the following

declare @n int = 0
update JazzCarryOver
set @n = @n+10,
   batch = @n
0
 
LVL 2

Author Closing Comment

by:alcindor
Comment Utility
Thanks guys,
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now