Cursor Update issue

Question

Cursor Update issue

iseedeadpeople

Mr or Mrs. 500

Points: 566
More actions
March 21, 2018 at 6:24 am

#344447

Hi guys i try to update 400 rows with cursor and when i try it i get this error
Msg 16929, Level 16, State 1, Line 14 The cursor is READ ONLY.
Actually i have a primary key and unique index ... Maybe the script is wrong. Check
Thank you 🙂
DECLARE Update_CURSOR CURSOR FOR
SELECT DISTINCT ID,SiteID FROM ACCOUNTS
WHERE SiteID IN (25,49) AND
UserTypeID = 2 AND
LastLoginDate > '2018-01-01'
OPEN Update_CURSOR
FETCH FROM Update_CURSOR
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE Accounts SET SiteID = 101 WHERE CURRENT OF Update_CURSOR
FETCH NEXT FROM Update_CURSOR
END
CLOSE Update_CURSOR
DEALLOCATE Update_CURSOR

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246963 More actions · Answer 1

iseedeadpeople - Wednesday, March 21, 2018 6:24 AM
Hi guys i try to update 400 rows with cursor and when i try it i get this error
Msg 16929, Level 16, State 1, Line 14 The cursor is READ ONLY.
Actually i have a primary key and unique index ... Maybe the script is wrong. Check
Thank you 🙂
DECLARE Update_CURSOR CURSOR FOR
SELECT DISTINCT ID,SiteID FROM ACCOUNTS
WHERE SiteID IN (25,49) AND
UserTypeID = 2 AND
LastLoginDate > '2018-01-01'
OPEN Update_CURSOR
FETCH FROM Update_CURSOR
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE Accounts SET SiteID = 101 WHERE CURRENT OF Update_CURSOR
FETCH NEXT FROM Update_CURSOR
END
CLOSE Update_CURSOR
DEALLOCATE Update_CURSOR

No need for a cursor. Just do this (after testing, of course).
UPDATE Accounts SET SiteID = 101 WHERE SiteID IN ( 25, 49 ) AND UserTypeID = 2 AND LastLoginDate > '20180101';

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

iseedeadpeople Mr or Mrs. 500 Points: 566 More actions · Answer 2

Yea, Thanks.I'll test now with this script, but why this cursor dont work 🙁

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

iseedeadpeople - Wednesday, March 21, 2018 6:31 AM
Yea, Thanks.I'll test now with this script, but why this cursor dont work 🙁

Not using cursors for updates, I do write many for specific processing requirements, I would say that you didn't define the cursor as an updatable cursor. The default based on your declaration is most likely a read_only cursor.

Having said that, doing things in a set based fashion is better and the way you should be writing your SQL code. Not saying that cursors are wrong, just not the right tool for the job you were trying to accomplish.

x SSC-Insane Points: 23660 More actions · Answer 4

Here's an example of updating with a cursor:

https://www.codeproject.com/Articles/232452/How-to-Use-Update-Cursors-in-SQL-Server