From: Josh Berkus <josh@agliodbs.com>
To: Jeff Kowalczyk <jtk@yahoo.com>
To: 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
Message-ID: <200310290942.57584.josh@agliodbs.com> (raw)
In-Reply-To: <pan.2003.10.29.14.52.52.251007@yahoo.com>
References: <pan.2003.10.29.14.52.52.251007@yahoo.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
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: josh@agliodbs.com, jtk@yahoo.com
Subject: Re: Help on update that subselects other records in table, uses joins
In-Reply-To: <200310290942.57584.josh@agliodbs.com>
* 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