Cursor not updating records correctly

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Cursor not updating records correctly

Author

Topic

Clarie
Starting Member

3 Posts

Posted - 2007-10-19 : 12:11:58

I'm stuck with the cursor posted below. It runs, but updates all records with a 0 (seed start at 510500) and it does not update the records in the destination table one at a time. Can someone please tell me what I'm doing wrong? Many thanks, Clarie

DECLARE @counter int
DECLARE @seed int
DECLARE @numOfRecords int
SET @seed = 510500
set @counter = @seed
SET @numOfRecords = (select count(*) from acuheader)
DECLARE nextRow CURSOR
FOR
SELECT apar_id FROM acuheader
FOR update of apar_id
OPEN nextRow
FETCH NEXT FROM nextRow INTO @counter
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE acuheader
SET apar_id = @counter
SET @counter = @counter+1

FETCH NEXT FROM nextRow INTO @counter

END
CLOSE nextRow
DEALLOCATE nextRow

tm
Posting Yak Master

160 Posts

Posted - 2007-10-19 : 13:53:08

Try changing ..

from:
Update acuheader set apar_id = @counter

to:
update acuheader set apar_id = @counter where current

refer to books on line .. cursors [SQL Server], updates

Clarie
Starting Member

3 Posts

Posted - 2007-10-19 : 14:19:30

I changed to and it is still updating with all zeros. I checked out books online, but still can't seem to get the counter to increment, let alone take the original seed.
I now have...
update acuheader set apar_id = @counter where current of nextRow

quote:
Originally posted by tm

Try changing ..

from:
Update acuheader set apar_id = @counter

to:
update acuheader set apar_id = @counter where current

refer to books on line .. cursors [SQL Server], updates

tm
Posting Yak Master

160 Posts

Posted - 2007-10-19 : 14:42:01

I noticed you are fetching from table and then incrementing ..

FETCH NEXT FROM nextRow INTO @counter
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE acuheader
SET apar_id = @counter
SET @counter = @counter+1 << Adding one to the retrieved value stored in @counter

FETCH NEXT FROM nextRow INTO @counter << getting value into @counter

Therefore you had two problems.
1. When you update you did not have where current
2. you should be using a separate variable (Should this be @Seed?)

Clarie
Starting Member

3 Posts

Posted - 2007-10-19 : 15:26:06

quote:
Originally posted by tm
1. When you update you did not have where current
2. you should be using a separate variable (Should this be @Seed?)

1. I updated the WHERE CURRENT OF clause (which didn't help)
2. I changed the variables that were being fetched and updated; it works now, thanks for all the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources