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;