From: Tom Lane <tgl@sss.pgh.pa.us>
To: Carolyn Wong <carolyn@greatpacific.com.au>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: update help
Date: Wed, 17 Jan 2001 22:18:59 -0500
Message-ID: <22930.979787939@sss.pgh.pa.us> (raw)
In-Reply-To: <3A665AE3.CE43178B@greatpacific.com.au>
References: <3A665AE3.CE43178B@greatpacific.com.au>
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: tgl@sss.pgh.pa.us, carolyn@greatpacific.com.au
Subject: Re: update help
In-Reply-To: <22930.979787939@sss.pgh.pa.us>
* 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