Solved

Update using FOR UPDATE clause

Posted on 2000-02-17
5
525 Views
Last Modified: 2008-02-20
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.
0
Comment
Question by:srikumar_p
5 Comments
 
LVL 4

Expert Comment

by:Gustavo Perez Buenrostro
Comment Utility
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
 

Author Comment

by:srikumar_p
Comment Utility
But I am not using a view. I am using a table
0
 
LVL 2

Expert Comment

by:ginde
Comment Utility
Use
DECLARE test_cur CURSOR LOCAL FOR SELECT '1' FROM test
FOr UPDATE

Also, use FETCH NEXT instead of FETCH
-Ginde



0
 

Author Comment

by:srikumar_p
Comment Utility
Tried this. It doesn't help.
0
 
LVL 8

Accepted Solution

by:
chigrik earned 50 total points
Comment Utility
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

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now