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

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