Solved

update and cursor

Posted on 2001-06-11
5
688 Views
Last Modified: 2007-11-27
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
0
Comment
Question by:rafistern
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 18

Accepted Solution

by:
nigelrivett earned 100 total points
ID: 6176680
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
 
LVL 4

Author Comment

by:rafistern
ID: 6176943
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
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6177149
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
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6177159
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
 
LVL 4

Author Comment

by:rafistern
ID: 6177203
Got it working. I made it even simpler in the end.

Thanks
0

Featured Post

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

756 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