Received: from sss.pgh.pa.us (sss.pgh.pa.us [209.114.132.154])
	by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0I4J7096319
	for <pgsql-sql@postgresql.org>; Wed, 17 Jan 2001 23:19:07 -0500 (EST)
	(envelope-from tgl@sss.pgh.pa.us)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.11.1/8.11.1) with ESMTP id f0I4IvJ23148;
	Wed, 17 Jan 2001 23:18:57 -0500 (EST)
To: Carolyn Lu Wong <carolyn@greatpacific.com.au>
cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Re: update help 
In-reply-to: <3A666A8F.591B8315@greatpacific.com.au> 
References: <3A665AE3.CE43178B@greatpacific.com.au>
	<22930.979787939@sss.pgh.pa.us>
	<3A666A8F.591B8315@greatpacific.com.au>
Comments: In-reply-to Carolyn Lu Wong <carolyn@greatpacific.com.au>
	message dated "Thu, 18 Jan 2001 15:01:19 +1100"
Date: Wed, 17 Jan 2001 23:18:57 -0500
Message-ID: <23145.979791537@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Archive-Number: 200101/208
X-Sequence-Number: 873

Carolyn Lu Wong <carolyn@greatpacific.com.au> writes:
> Forgot to mention that I'm using V6.5.

Oh.  6.5's support for sub-selects is pretty limited :-(.
I think the only way to do it in 6.5 is with a temp table,
eg

SELECT id, sum(amount) as sum into temp table tt from t2 group by id;

update t1 set amount = tt.sum where id = tt.id;

drop table tt;

You might wanna think about updating sometime soon...

			regards, tom lane