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@goldfisch.at
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly

--
Martijn van Oosterhout   <kleptog@svana.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.