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';
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply