Message-ID: <3A666A8F.591B8315@greatpacific.com.au>
Date: Thu, 18 Jan 2001 15:01:19 +1100
From: Carolyn Lu Wong <carolyn@greatpacific.com.au>
Organization: Great Pacific Financial Group
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Re: update help
References: <3A665AE3.CE43178B@greatpacific.com.au>
	<22930.979787939@sss.pgh.pa.us>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Forgot to mention that I'm using V6.5. It doesn't seem to like
subqueries, got the following error:

ERROR:  parser: parse error at or near "select"

What I really want to do is follows

t2:	ID	Amount ........
	-----------------------
	1	1	......
	1	2	......
	2	3	......
	2	2	......

and want the following result in t1

t1:	ID	Amount .......
	-----------------------
	1	3	......
	2	5	......

Tom Lane wrote:
> 
> 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