Solved

update and cursor

Posted on 2001-06-11
5
647 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
  • 3
  • 2
5 Comments
 
LVL 18

Accepted Solution

by:
nigelrivett earned 100 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Got it working. I made it even simpler in the end.

Thanks
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now