Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: Jeff Kowalczyk <jtk@yahoo.com>, pgsql-sql@postgresql.org
Subject: Re: Help on update that subselects other records in table, uses joins
Date: Wed, 29 Oct 2003 09:42:57 -0800
User-Agent: KMail/1.4.3
References: <pan.2003.10.29.14.52.52.251007@yahoo.com>
In-Reply-To: <pan.2003.10.29.14.52.52.251007@yahoo.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200310290942.57584.josh@agliodbs.com>

Jeff,

> UPDATE ordercharges INNER JOIN orders ON
> orders.orderid = ordercharges.orderid
> SET orderchargeasbilled =

You may only UPDATE one table at a time, you can't update a JOIN.  So when 
selecting from another table to filter or calculate your update, the form is:

UPDATE orderchanges 
SET orderchargesbilled = {expression}
FROM orders
WHERE orders.orderid = ordercharges.orderid
	AND etc.

Second, your value expression for the update is a subselect which includes a 
select on the table and field you are updating!  This is a recursive loop and 
a very bad idea; gods only know what you'll end up with.

I suggest Joe Conway's "SQL for Smarties"  or "SQL Queries for Mere Mortals" 
from another author.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco