"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Tuesday, December 31, 2024, PG Bug reporting form <noreply@postgresql.org>
> wrote:
>> UPDATE stgtbl_837465r4 STGTBL
>> SET ATTR_7 = PVQUERY.batch_number, ATTR_1 = PVQUERY.batch_number , TF_1 =
>> 'S'
>> FROM (
>> SELECT CAST(BATCH_SEQ AS NUMERIC) AS BATCH_NUMBER,
>> CAST(BATCH_SEQ AS VARCHAR(50)) AS BATCH,
>> BATCH_TEXT
>> FROM (
>> SELECT NEXTVAL('sample_sequence__2899') AS BATCH_SEQ,
>> 'A' AS BATCH_TEXT
>> FROM test_act
>> ) A
>> ) PVQUERY
>> WHERE PVQUERY.batch_text IS NOT NULL;
> You never equate the rows of PVQUERY to the rows of stgtbl_837465r4.
> Tables are not ordered so unless you write what is basically a join
> condition if the where clause an update-from query is malformed and you end
> up with non-sensical random changes (well, unless the from query only
> produces a single row).
Yeah, this is a faulty query. But I'd explain it slightly
differently: when you write UPDATE ... FROM ..., you are writing
a join between the update target table and whatever is in FROM.
For predictable results, you must write a WHERE clause that
ensures that each row of the target table joins to at most one
row of FROM. For any given row of the target table, what
happens is:
* If there is no join partner row, nothing happens.
* If there is one join partner row, the target row is
updated using the values from that partner row.
* If there is more than one join partner row, the target row is
updated using the values from some unspecified partner row.
The described symptoms sound like the same FROM row was used
to update all of the target rows, but that's only one of the
possible outcomes from this underspecified join.
Another problem with the query as written is that I don't think
we guarantee that the sub-select involving NEXTVAL will be
executed exactly once. I'd put that in a WITH clause to be safe.
regards, tom lane
