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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
