[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

update and cursor

Posted on 2001-06-11
5
Medium Priority
?
712 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 400 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

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

612 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