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.
srikumar_pAsked:
Who is Participating?
 
chigrikConnect With a Mentor Commented:
Try this:

SET NOCOUNT ON
GO

if object_id('dbo.test') is not null drop table test
GO

CREATE TABLE test(
  name varchar (20)
)
GO

insert into test VALUES ('Test1')
insert into test VALUES ('Test2')
insert into test VALUES ('Test3')
GO

select * from test
GO

BEGIN

DECLARE @v_dummy CHAR(1)
DECLARE test_cur CURSOR 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
CLOSE test_cur
DEALLOCATE test_cur
GO

select * from test
GO


It's result:

"
name                
--------------------
Test1                
Test2                
Test3                
name                
--------------------
test_name            
test_name            
test_name            
"
0
 
Gustavo Perez BuenrostroCommented:
srikumar_p,
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.
0
 
srikumar_pAuthor Commented:
But I am not using a view. I am using a table
0
 
gindeCommented:
Use
DECLARE test_cur CURSOR LOCAL FOR SELECT '1' FROM test
FOr UPDATE

Also, use FETCH NEXT instead of FETCH
-Ginde



0
 
srikumar_pAuthor Commented:
Tried this. It doesn't help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.