Re: renumber table

On Thu, Jun 19, 2008 at 7:54 AM, Steve Clark <sclark(at)netwolves(dot)com> wrote:

> I am not sure that will do what I want. As an example
> suppose I have 5 rows and the idfield is 1,2,3,4,5
> now row 1 is updated, not the idfield but another column, then row 3 is
> deleted.
> Now I would like to renumber them such that 1 is 1, 2 is 2, 4 is 4 , 5 is
> 4.
>
> I don't think what you wrote will necessarily keep them in the same relative
> order that they
> were before.

It's ugly and a hack, but if you could do:

1. rename the table
2. create a new copy of the table. give the idfield a sequence.
3. select all the records from the renamed table, ordering by idfield,
and insert all fields other than idfield into the new table.
4. drop the renamed table

You'll maintain ordering and have a nice sequential idfield.
--
- David T. Wilson
david(dot)t(dot)wilson(at)gmail(dot)com