Solved

update and cursor

Posted on 2001-06-11
5
661 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
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

932 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

14 Experts available now in Live!

Get 1:1 Help Now