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

On Tue, Jun 21, 2005 at 03:23:07PM +0200, peter pilsl wrote:
> 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.

Well, that's because you're typing the query wrong. Because you said:

where t2.id <= voev_content.id

It's going to order them by the id (which you didn't show in your query
which is why it's not obvious). If you want to order by rank you should
do (your query search-replace id for rank):

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

Hope this helps,

--
Martijn van Oosterhout <kleptog(at)svana(dot)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.