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(dot)r(at)sbcglobal(dot)net> wrote in message
news:49F748CD(dot)9010108(at)sbcglobal(dot)net(dot)(dot)(dot)
> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>