X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 0A5A4D1B4E1
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon,  5 Jan 2004 11:11:28 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 45950-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon,  5 Jan 2004 07:10:40 -0400 (AST)
Received: from anchor-post-37.mail.demon.net (anchor-post-36.mail.demon.net
	[194.217.242.86])
	by svr1.postgresql.org (Postfix) with ESMTP id 84568D1B4CC
	for <pgsql-sql@postgresql.org>; Mon,  5 Jan 2004 07:10:32 -0400 (AST)
Received: from mwynhau.demon.co.uk ([193.237.186.96]
 helo=mainbox.archonet.com)
	by anchor-post-37.mail.demon.net with esmtp (Exim 3.35 #1)
	id 1AdSd0-0009sg-0b; Mon, 05 Jan 2004 11:10:34 +0000
Received: by mainbox.archonet.com (Postfix, from userid 529)
	id 5C6DE1693D; Mon,  5 Jan 2004 11:10:33 +0000 (GMT)
Received: from client17.archonet.com (client17.archonet.com [192.168.1.17])
	by mainbox.archonet.com (Postfix) with ESMTP
	id 93DB31692C; Mon,  5 Jan 2004 11:10:30 +0000 (GMT)
From: Richard Huxton <dev@archonet.com>
To: John Coryat <john@coryat.com>, pgsql-sql@postgresql.org
Subject: Re: Complex Update
Date: Mon, 5 Jan 2004 11:10:30 +0000
User-Agent: KMail/1.5
References: <3FF6896E.3010602@coryat.com>
In-Reply-To: <3FF6896E.3010602@coryat.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200401051110.30065.dev@archonet.com>
X-Bogosity: No, tests=bogofilter, spamicity=0.000000, version=0.15.3
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200401/28
X-Sequence-Number: 16421

On Saturday 03 January 2004 09:20, John Coryat wrote:
> Problem: I have a table that has a field I want to plug with a value
> from other tables.
[snip]
> I would expect it to look something like this:
>
> update orderheader set total = (select sum(amount) from orderdetail
> where orderdetail.orderno=orderheader.orderno) ;

Looks fine to me. In general, you can do something of the form:

UPDATE ta SET a2 = (SELECT expr(b2) FROM tb WHERE b1=a1);

NOTE - make sure you understand what happens when there is no b1=a1 and what 
that will mean to your particular setup.

-- 
  Richard Huxton
  Archonet Ltd