Carolyn Wong <carolyn@greatpacific.com.au> writes:
> I'd like to know what's the correct SQL statement to do the following:
> update t1 a
> set a.amount = sum(b.amount)
> from t2 b
> where a.id = b.id
Try
UPDATE t1 SET amount = (select sum(b.amount) from t2 b WHERE t1.id = b.id);
Or possibly you want
UPDATE t1 SET amount = (select sum(b.amount) from t2 b WHERE t1.id = b.id)
WHERE EXISTS (select * FROM t2 b WHERE t1.id = b.id);
depending on whether you mean to zero out any t1 rows that have no
matching rows in t2. Note that you cannot attach an alias name to the
target table, you have to use its real name in the subselects.
There was a thread about this very issue a few months ago, and IIRC
we decided that an aggregate in an UPDATE doesn't have well-defined
semantics. The SQL92 spec explicitly disallows it. Right now PG will
take it, but we probably do something pretty surprising :-(
regards, tom lane