Re: UPDATE... FROM - will ORDER BY not respected?

Carlo Stonebanks wrote:
> (FOR PG VERSION 8.3.6 running on Windows 2003 Server x64)
>
> We have a function that assigns unique ID's (to use as row identifiers)
> to a table via an UPDATE using nextval(). This table is imported from
> another source, and there is a "sequencing" field to let the query know
> in which order to assign the row identifiers. (Please do not confuse the
> sequencing field with a sequence value from nextval())
>
> The UPDATE command gets the order of the rows to update using a FROM
> clause, which in turn reads from a sub-query to get the rows in the
> order of "seq".
>
> The problem is that the UPDATE is NOT behaving as if it is receiving the
> sequence identifiers in the order specified. In fact, it appears it is
> returned in REVERSE order (assigning id's in reverse order based on the
> values in seq)
>
> Here is the essence of the query (further below you will find the full
> DDL code of the function).
>
> UPDATE impt_table
> SET id = nextval(''id_seq'')
> FROM
> (SELECT seq
> FROM impt_table
> WHERE id IS NULL
> ORDER BY seq
> ) AS empty_ids
> WHERE
> impt_table.seq = empty_ids.seq
> AND impt_table.id IS NULL;
>
> Was I wrong in assuming that the UPDATE would respect the order of rows
> coming out of the sub-clause? Is there a better way to do this?
>
> Thanks, Carlo
>

I think the ORDER BY is free to update the rows in any order it needs
to. The key is to put the sequence further down. How about this?

UPDATE impt_table
SET id = newid
FROM
SELECT seq, nextval('id_seq') as newid
FROM (SELECT seq
FROM impt_table
WHERE id IS NULL
ORDER BY seq
) AS pre_empty_ids ) as empty_ids
WHERE
impt_table.seq = empty_ids.seq
AND impt_table.id IS NULL;