March 21, 2018 at 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
March 21, 2018 at 6:28 am
iseedeadpeople - Wednesday, March 21, 2018 6:24 AMHi 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 = 0BEGIN
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';
March 21, 2018 at 6:31 am
Yea, Thanks.I'll test now with this script, but why this cursor dont work
March 21, 2018 at 7:34 am
iseedeadpeople - Wednesday, March 21, 2018 6:31 AMYea, 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.
March 21, 2018 at 7:46 am
Here's an example of updating with a cursor:
https://www.codeproject.com/Articles/232452/How-to-Use-Update-Cursors-in-SQL-Server
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy