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

> 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?

Adam - thanks. Unless I hear otherwise I will assume that you mean the
UPDATE is free to update the rows in any way it wants - irregardless of how
whether the data return in the FROM clause is ordered.

I also appreciate the time you took to re-write the query for me. Thank you
very much for taking the time to reply.

Carlo


"Adam Rich" <adam.r@sbcglobal.net> wrote in message
news:49F748CD.9010108@sbcglobal.net...
> 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;
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>