Solved

Update using FOR UPDATE clause

Posted on 2000-02-17
5
566 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
ID: 2532688
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
ID: 2532713
But I am not using a view. I am using a table
0
 
LVL 2

Expert Comment

by:ginde
ID: 2532794
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
ID: 2532812
Tried this. It doesn't help.
0
 
LVL 8

Accepted Solution

by:
chigrik earned 50 total points
ID: 2533594
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

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

730 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