Re: [SQL] found a way to update a table with data from another one

Patrick JACQUOT <patrick(dot)jacquot(at)anpe(dot)fr> writes:
> I found a simpler way to do the job I wanted done

> UPDATE t1 SET balance = balance+t2.amount WHERE EXISTS (SELECT *
> FROM t2 WHERE t2.id = t1.id)

> DOES WORK PROPERLY.

Does it? I don't think that this will do what you are expecting,
because t2 in the outer query is not the same table reference as t2 in
the inner query --- there's an implicit FROM t2 in the outer query.
What this'll actually do is perform an unrestricted join of *all* rows
in t2 to each row in t1 that meets the WHERE condition (ie, has some
matching row in t2).

Because of the visibility rules for updates, only one of the joined
pairs for each t1 row will actually get into the final result ---
but it's unlikely to be the one you want. When I tried it, it seemed
the first row to be processed in the t2 table got added to all the
t1 rows that had matches:

regression=> select * from t1;
id|balance
--+-------
1| 100
2| 1000
3| 0
(3 rows)

regression=> select * from t2;
id|amount
--+------
1| 44
2| 55
(2 rows)

regression=> UPDATE t1 SET balance = balance+t2.amount WHERE EXISTS (SELECT *
regression-> FROM t2 WHERE t2.id = t1.id);
UPDATE 2
regression=> select * from t1;
id|balance
--+-------
3| 0
1| 144
2| 1044
(3 rows)

I suspect the effect you really want is much simpler:

UPDATE t1 SET balance = balance+amount FROM t2 WHERE t1.id = t2.id;

That gives me

regression=> select * from t1;
id|balance
--+-------
3| 0
1| 188
2| 1099
(3 rows)

so this time the amounts went to the proper places...

regards, tom lane