Re: How to update record in a specified order

JLL,

So, you want to update a field with a NEXTVAL counter, but with the counter
ordered by another column?

If so, you will have to use a procedure. Ordering your UPDATEs is not part
of SQL -- it requires a procedural element. Here's a simple procedure (you
debug it):

CREATE PROCEDURE add_my_table_counter ()
RETURNS TEXT AS '
DECLARE v_rec RECORD;
BEGIN
WHILE v_rec IN SELECT * FROM my_table ORDER BY last_name LOOP
UPDATE my_table SET counter_field = NEXTVAL(''my_sequence'')
WHERE my_table.id = v_rec.id;
END LOOP;
RETURN ''Done updating.'';
END;'
LANGUAGE 'plpgsql';

--
-Josh Berkus
Aglio Database Solutions
San Francisco