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

Martijn van Oosterhout wrote:
> How about:
>
> update table set id = (select count(*) from table t2 where t2.id <= table.id);
>
> Ugly as hell, but it should work.
>


thnx a lot. But it does not work as expected cause the update-statement
ist not commiting for the whole table during the execution. So the
resulting order can be different from the original order, which is what
I try to avoid.


example with real-work-database. entries with rank=0 are excluded from
the query.


knowledge=# select  rank,kategorie,titel from voev_content where
kategorie=5 order by rank;

  rank | kategorie |        titel
------+-----------+----------------------
     0 |         5 | hauptaktivitäten
     3 |         5 | test
     4 |         5 | startseite
     5 |         5 | Salzburger Gespräche
(4 rows)

knowledge=# update voev_content set rank = (select count(*) from
voev_content t2 where t2.id <= voev_content.id and t2.kategorie=5 and
t2.id !=0) where kategorie=5 and rank!=0;

UPDATE 3


knowledge=# select  rank,kategorie,titel from voev_content where
kategorie=5 order by rank;
  rank | kategorie |        titel
------+-----------+----------------------
     0 |         5 | hauptaktivitäten
     1 |         5 | Salzburger Gespräche
     2 |         5 | test
     3 |         5 | startseite
(4 rows)


note that test now is ordered as second (excluding the rank=0-entry)
while it was ordered first in the original configuration.

thnx,
peter


> 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
>
>


--
mag. peter pilsl
goldfisch.at
IT-management
tel +43 699 1 3574035
fae +43 699 4 3574035
pilsl@goldfisch.at