Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id 01FB19FB5B8
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri,  6 Apr 2007 14:02:21 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024)
	with ESMTP id 60447-07 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri,  6 Apr 2007 14:02:08 -0300 (ADT)
X-Greylist: delayed 00:05:01.336077 by SQLgrey-1.7.4
Received: from kcout02.prserv.net (kcout02.prserv.net [12.154.55.32])
	by postgresql.org (Postfix) with ESMTP id CCB0C9FB53B
	for <pgsql-sql@postgresql.org>; Fri,  6 Apr 2007 14:02:14 -0300 (ADT)
Received: from kcreg02 (kcreg02.backbone.kan[10.254.3.132])
	by prserv.net (kcout02) with SMTP
	id <2007040616570620200gf51te>; Fri, 6 Apr 2007 16:57:06 +0000
X-Originating-IP: [10.254.3.132]
From: paallen@attglobal.net
To: pgsql-sql@postgresql.org,
Subject: Update Field with function/data from other tables?
Date: Fri, 06 Apr 2007 16:57:06 +0000
X-Priority: 3
MIME-Version: 1.0
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <20070406170214.CCB0C9FB53B@postgresql.org>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200704/85
X-Sequence-Number: 28085

Hi all,

I want to update the values of a column with the
result of a function that requires information
from another table.  Specifically it needs a
minium and maximum value from another table.

How do I perform the update query?

Below is my attempt but it doesn't work.  The
"bhlineid" is my primary key and foreign key in
the 2 tables

UPDATE geo_obs.bhline SET bhline_geom =
CalcBH3dLineMC (minmax.bhlineid,
minmax.srid_original, minmax.from_m, minmax.to_m, 
			'geo_obs.bhline_surv', 'bhlineid',  'dist_m',
'azim', 'v_inclin',
			'bhline_surv_geom', 5.0)
	FROM 
(SELECT i.bhlineid AS bhlineid, min(i.from_m) AS
from_m, 
max(i.to_m) AS to_m, max(c.srid_original) AS
srid_original 
FROM geo_obs.bhline_int AS i, geo_obs.bhline AS c 
WHERE c.bhlineid=i.bhlineid 
GROUP BY i.bhlineid) AS minmax

WHERE geo_obs.bhline.bhlineid = minmax.bhlineid ;


Thanks for any help.

Phillip Allen