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

On 2009-04-28, Carlo Stonebanks <stonec.register@sympatico.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;