Cursor does not update last record

I have the attached SQL script. I am selecting an initial recordset of 100 records and looping through each to update accordingly but it appears that the update is only applied to the first 99 records (the 100th record is not being update)...am I missing something?
declare
		@RecordID int,
		@Price int

declare csr cursor for

	Select top 100 a.RecordID
	FROM Records
	ORDER BY
		a.RecordDate desc

	open csr

	fetch next from csr into
			@RecordID

	while @@FETCH_STATUS = 0
		begin  --cursor loop

			set @Price = (SELECT Price
							FROM Records 
			where RecordID = @RecordID)

			if @Price > 0
				begin
					update Records
					set Priced = 1
					where RecordID = @RecordID
				end
			else
				begin
					update Records
					set Priced = 0
					where RecordID = @RecordID
			end

			fetch next from csr into
					@RecordID
		end  --cursor loop

	close csr
	deallocate csr

Open in new window

felkamauAsked:
Who is Participating?
 
cyberkiwiConnect With a Mentor Commented:
Re why the cursor is updating only 99

(1) do you have duplicate RecordIDs? top 100 could include a pair of duplicates
(2) the cursor may have to be declared static to get a static copy of the first 100 records

declare csr cursor STATIC for
0
 
cyberkiwiCommented:
;WITH top100 as (
      Select top 100 a.RecordID
      FROM Records
      ORDER BY a.RecordDate desc)
UPDATE Records
Set Priced= case when Price>0 then 1 else 0 end
0
 
cyberkiwiCommented:
Sorry! Update top 100 only

;WITH top100 as (
      Select top 100 a.RecordID
      FROM Records
      ORDER BY a.RecordDate desc)
UPDATE top100
Set Priced= case when Price>0 then 1 else 0 end
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

 
_bmendozaCommented:
I see no problem except for "a" alias you'll get

The multi-part identifier "a.RecordID" could not be bound.
The multi-part identifier "a.RecordDate desc" could not be bound.

why not  just do the following?

alter table Records
drop column Priced

alter table Records
add Priced as case when price > 0 then 1else 0 end

or there's something else as a reason for using cursor.
0
 
felkamauAuthor Commented:
declare STATIC works.
Thank You!
0
 
cyberkiwiCommented:
Just FYI, but if the cursor does all that it shown above (and only that), you only need the update query... which will be only 10-100 times faster.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.