From: Carolyn Lu Wong <carolyn@greatpacific.com.au>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: update help
Date: Thu, 18 Jan 2001 15:01:19 +1100
Message-ID: <3A666A8F.591B8315@greatpacific.com.au> (raw)
References: <3A665AE3.CE43178B@greatpacific.com.au>
<22930.979787939@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: carolyn@greatpacific.com.au, tgl@sss.pgh.pa.us
Subject: Re: update help
In-Reply-To: <3A666A8F.591B8315@greatpacific.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox