Renumbering records in table

[Johnny]

Hello all,

I´m trying to renumber records in table and need some advise!
The table have two fields:
PstId is a number
PstID PstDesign
1 John
2 Paul
3 Carl ...etc

I want to be able to add a new record with PstDesign="Smith" and his
PstID=3. The number 3 already exists in table but intention of the
number is to provide ranking to people. In this case Smith should have
higher rank than Carl. So, Carl should automatically became number 4
and all the other after Carl should add 1 to their Rank(PstID).
I´m lack of knowledge to do that, but enough knowledge to learn from
you experts out there!

Thanks in advance
Johnny

 

[Tom van Stiphout]

On Sat, 26 Apr 2008 09:47:12 -0700 (PDT), Johnny

Your column name PstID threw me off for a moment. Perhaps StdSortOrder
would be a better name, and there would be another primary key field
in this table.
The standard way to do this is to run some update queries to move the
numbers out of the way:
update MyTable
set StdSortOrder = StdSortOrder + 1
where StdSortOrder >= 3

followed by your insert with StdSortOrder=3.

An alternative is to use a Double for the StdSortOrder field, in which
case you don't have to move anything out of the way. You just insert
your new record with StdSortOrder=2.5
Then if you want another one after 2 and before 2.5, you use 2.25
Simply get Before+After/2

-Tom.