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.