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

On 2009-04-28, Carlo Stonebanks <stonec(dot)register(at)sympatico(dot)ca> 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?

try this:

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