> main_table: id, name, position
> key_table: id, main_table_id, key, value
>
> Here is how I need to sort the records:
> SELECT * FROM main_table
> INNER JOIN key_table ON main_table.id = key_table.main_table_id
> WHERE key = 'param'
> ORDER BY value
>
> I currently collect all ids from main_table in sorted order and then
> update the position field for each row in the main_table one-by-one. Is
> there a better/faster/more efficient solution?
A cheap solution if you don't care about the position value as long as
sort order is ok.
1)
# SELECT main_table.id into temp_table FROM main_table INNER JOIN
key_table ON main_table.id = key_table.main_table_id ORDER BY value;
2)
# update main_table set position = (select oid from temp_table where id =
main_table.id );
I guess I'll get a set of consecutive oids by this.
You can make the number begin at arbitrary number, by
2-a)
# update main_table set position = ( (select oid::int4 from temp_table
where id = main_table.id ) - (select min(oid::int4) from temp_table) + 1)
;
I read that oid wraps around (after ~ billions) so you might want to check
your current oid.
Regards,
Ben K.
Developer
http://benix.tamu.edu