?
Solved

Update using FOR UPDATE clause

Posted on 2000-02-17
5
Medium Priority
?
581 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 150 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 Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

864 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