srikumar_p
asked on
Update using FOR UPDATE clause
Please look at the following piece of code
BEGIN
DECLARE v_dummy CHAR(1)
DECLARE test_cur CURSOR LOCAL FOR SELECT '1' FROM test
OPEN test_cur
FETCH test_cur INTO @v_dummy
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE test set
name = 'test_name'
WHERE CURRENT OF test_cur
FETCH test_cur INTO @v_dummy
END
END
GO
When executed, this is giving the following error
Server: Msg 16947, Level 10, State 1, Line 32287536
No rows were updated or deleted.
The statement has been terminated.
Any idea what is going on?
Thanks in advance.
BEGIN
DECLARE v_dummy CHAR(1)
DECLARE test_cur CURSOR LOCAL FOR SELECT '1' FROM test
OPEN test_cur
FETCH test_cur INTO @v_dummy
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE test set
name = 'test_name'
WHERE CURRENT OF test_cur
FETCH test_cur INTO @v_dummy
END
END
GO
When executed, this is giving the following error
Server: Msg 16947, Level 10, State 1, Line 32287536
No rows were updated or deleted.
The statement has been terminated.
Any idea what is going on?
Thanks in advance.
ASKER
But I am not using a view. I am using a table
Use
DECLARE test_cur CURSOR LOCAL FOR SELECT '1' FROM test
FOr UPDATE
Also, use FETCH NEXT instead of FETCH
-Ginde
DECLARE test_cur CURSOR LOCAL FOR SELECT '1' FROM test
FOr UPDATE
Also, use FETCH NEXT instead of FETCH
-Ginde
ASKER
Tried this. It doesn't help.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Read article "Update on View-based Cursor Fails to Modify Row if View Contains a Join" provided by Microsoft Product Support Services.
Article ID: Q238547
http://support.microsoft.com/support/kb/articles/Q238/5/47.ASP?LNG=CAN&SA=&FR=1
PD: More info is needed for better advice.