Re: renumber id's in correct order (compact id's)

How about:

update table set id = (select count(*) from table t2 where t2.id <= table.id);

Ugly as hell, but it should work.

Hope this helps,

On Tue, Jun 21, 2005 at 10:06:40AM +0200, peter pilsl wrote:
>
>
> I've entries with id's like:
>
> x | id
> ---+----
> b | 1
> a | 4
> e | 5
> c | 12
> d | 19
> (5 rows)
>
>
> now I'd like to have the id in continuing number to get:
>
> x | id
> ---+----
> b | 1
> a | 2
> e | 3
> c | 4
> d | 5
> (5 rows)
>
>
> Simpliest way to do would be to create a sequence and update the whole
> table using nextval on the sequencec. Unfortunately UPDATE does not know
> about an order-statement.
>
> Any Idea,
> thnx,
> peter
>
>
>
>
>
> --
> mag. peter pilsl
> goldfisch.at
> IT-management
> tel +43 699 1 3574035
> fae +43 699 4 3574035
> pilsl(at)goldfisch(dot)at
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.