renumber a column non-autoincrement INT field

[Donkie]

Need help!

i need to renumber a sequence of numbers on a couple of records when a record is deleted. example:

Start :
value 2ndvalue
1 5766
2 3333
3 6679
4 8697
5 2845

now i'm deleting the row with the value: 2 so i get:
1, 3, 4, 5

i need to get the numbers back in a sequence as:
1, 2, 3, 4

now i can make it happen by assigning a number (value-1) to each field which comes after 2.

But when a table get really large eg. 34000 and i'm deleting number 2 it needs a lot of changing :-(

is there a way that mysql can do the job, that when the record is deleted, i can give mysql a command to renumber the whole column at once.

Any help would be kindly accepted.

Thx in advance.

 

[r937]

the command that you would give to mysql is the one you suggested yourself --

update thetable
set thevalue = thevalue - 1
where thevalue > 2

you have to do this after each single deletion (i.e. you cannot delete 23 and 67 and then issue one command to fix things up)

and yes, it takes bloody forever

blame whoever designed the sequential column


rudy

http://r937.com/

 

[Donkie]

what happens if the database get really big and more visitors are doing stuff at the same time, which sets the update function in action.?

Are there any steps i can take to speed things up. the table has 6 columns with 4 int fields and 2 varchar(100) fields and i expect more than 250000+ entries in that particular table. and more that 150 users a day on the part on which the update function can be called.

any suggestions?

 

[sleipnir214]

Why even worry about this? Just leave the gaps in the numbering. If you're worried about running out of numbers, set the integer column to be bigint unsigned.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[r937]

donkie, you used the word need twice

make sure it is a genuine need, otherwise do exactly what sleipnir214 said -- just forget about renumbering

if you really need to do the renumbering (and i cannot imagine why) then you will just have to shut down user access while the update is going on

tell that to your boss, and blame the person who designed it to need renumbering


rudy