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@xxxxxxxxxxxx
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx 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@xxxxxxxxxxxx
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
