Link to home
Start Free TrialLog in
Avatar of srikumar_p
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.
Avatar of Gustavo Perez Buenrostro
Gustavo Perez Buenrostro

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.
Avatar of srikumar_p

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



Tried this. It doesn't help.
ASKER CERTIFIED SOLUTION
Avatar of chigrik
chigrik

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial