Solved

Update using FOR UPDATE clause

Posted on 2000-02-17
5
552 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

810 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