Renumbering records in table

J

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
 
T

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top