To: Carolyn Wong <carolyn@greatpacific.com.au>
cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Re: update help 
In-reply-to: <3A665AE3.CE43178B@greatpacific.com.au> 
References: <3A665AE3.CE43178B@greatpacific.com.au>
Comments: In-reply-to Carolyn Wong <carolyn@greatpacific.com.au>
	message dated "Thu, 18 Jan 2001 13:54:27 +1100"
Date: Wed, 17 Jan 2001 22:18:59 -0500
Message-ID: <22930.979787939@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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