update and cursor

I have a table of entries in batch for a certain purpose. The entries have sequential numbers which are a primary key. I am writing a stored procedure which deletes a record from the batch and updates the sequential numbers to make them sequential again. This is the relevant part of the code.

Notes: "kod_mishloah" is the batch number, "siduri" is the sequential number


DELETE FROM tviot WHERE kod_mishloah = @mishloah AND siduri = @del

DECLARE @nextRecord INT
SET @nextRecord = @del
     
UPDATE tviot
     SET siduri = siduri +10000
     WHERE kod_mishloah = @mishloah AND siduri > @del

DECLARE TviaCursor CURSOR
FOR SELECT siduri FROM tviot WHERE kod_mishloah = @mishloah AND siduri > 10000
FOR UPDATE

OPEN TviaCursor

FETCH NEXT FROM TviaCursor
WHILE @@FETCH_STATUS = 0
BEGIN
     FETCH NEXT FROM TviaCursor
     UPDATE tviot
          SET siduri = @nextRecord
          WHERE CURRENT OF TviaCursor
     SET @nextRecord = @nextRecord + 1
END

CLOSE TviaCursor
DEALLOCATE TviaCursor


In this code I do the following steps:
1. First I add 10000 to every siduri after the deleted entry in order that I won't have key conflicts when I renumber.
2. I select the lines into a cursor "TviaCursor"
3. I update the lines in the table corresponding to the current cursor line

When I run this I get the message:
"The requested row is not in the fetch buffer."

I am new to cursors so there is probably a very elementary mistake here. Any ideas?

Thanks,

Rafi
LVL 4
rafisternAsked:
Who is Participating?
 
nigelrivettCommented:
Why use a cursor (ever)

I would have thought something like this might work.

DELETE tviot WHERE kod_mishloah = @mishloah AND siduri = @del

while @@rowcount <> 0
begin
select @del = @del + 1
UPDATE tviot
SET siduri = @del
WHERE kod_mishloah = @mishloah
and siduri = (select min(siduri) from tviot t2 where t2.kod_mishloah = @mishloah and siduri > @del)
end


0
 
rafisternAuthor Commented:
You're right, the cursor just confuses everything!

There is still a problem with your solution that I get a problem with duplicate values in the primary key (siduri).

Is it possible that even though I have deleted the line, it still reckons that that value exists in the column?
0
 
nigelrivettCommented:
Is it possible that even though I have deleted the line, it still reckons that that value exists in
the column?
No.

Is siduri unique or is kod_mishloah, siduri unique?
If the former then you will get this problem and would need

DELETE tviot WHERE siduri = @del

while @@rowcount <> 0
begin
select @del = @del + 1
UPDATE tviot
SET siduri = @del
WHERE siduri = (select min(siduri) from tviot t2 where siduri > @del)
end




0
 
nigelrivettCommented:
Oops the code should be
DELETE tviot WHERE kod_mishloah = @mishloah AND siduri = @del

select @del = @del - 1
while @@rowcount <> 0
begin
select @del = @del + 1
UPDATE tviot
SET siduri = @del
WHERE kod_mishloah = @mishloah
and siduri = (select min(siduri) from tviot t2 where t2.kod_mishloah = @mishloah and siduri > @del)
end

it was leaving the one after the one deleted and updating thenext one to have the same key.


0
 
rafisternAuthor Commented:
Got it working. I made it even simpler in the end.

Thanks
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.