Re: renumber table

Scott Marlowe wrote:
> On Wed, Jun 18, 2008 at 3:50 PM, Steve Clark <sclark(at)netwolves(dot)com> wrote:
>
>>I realize this is certainly not the best design - but at this point in time
>>it can't be changed. The table
>>is rarely updated and never concurrently and is very small, typically less
>>than 100 rows so there really is
>>no performance impact.
>
>
> Then the easiest way to renumber a table like that is to do something like:
>
> create temp sequence myseq;
> update table set idfield=nextval('myseq');
>
> and hit enter.
> and pray. :)
>
>
Hi Scott,

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.

Regards,
Steve