From: Erik Jones <erik@myemma.com>
To: Fabian Peters <lists.fabian@e-lumo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: UPDATE with WHERE clause on joined table
Date: Fri, 28 Jul 2006 18:30:32 -0500
Message-ID: <44CA9E18.8020306@myemma.com> (raw)
In-Reply-To: <B45EBA38-2DFC-45E7-921B-16D52A986E7A@e-lumo.com>
References: <B45EBA38-2DFC-45E7-921B-16D52A986E7A@e-lumo.com>
Fabian Peters wrote:
> Hi,
>
> I'm only very rarely using SQL, so please forgive me if I show any
> obvious signs of ignorance...
>
> I've got three tables "customer", "address" and "country". I want to
> set the "language" attribute on "customer" on rows returned by a
> SELECT such as this:
>
> SELECT title, first_names, last_name, email, language,
> country.country_name FROM ((customer JOIN address ON customer.eoid =
> address.eoid_customer) JOIN country ON address.eoid_country =
> country.eoid) WHERE email LIKE '%.es' AND country.country_name = 'SPAIN';
>
> That is, I want to set the "language" to 'Spanish' where the
> "customer.email" is like '%.es' and where "country.country_name" is
> 'SPAIN'.
>
> I've tried all sorts of places to put the JOIN and the WHERE clauses
> within the UPDATE statement, but I just don't get it.
>
> I'd be most grateful for any help...
>
> TIA
>
> Fabian
>
> P.S.: One of my sorry attempts looked like this - which updates all
> rows in "customer" so I figure the WHERE clause is not where it should
> be:
>
> UPDATE customer SET language = 'Spanish' FROM ((customer AS
> customer_address JOIN address ON customer_address.eoid =
> address.eoid_customer) JOIN country ON address.eoid_country =
> country.eoid) WHERE customer.email LIKE '%.es' AND
> country.country_name = 'SPAIN');
The FROM clause is where you put relations other than the one you are
updating. Try this:
UPDATE customer
SET language='Spanish'
FROM address ad, country co
WHERE customer.eoid=ad.eoid_customer AND ad.eoid_country=co.eoid
AND co.country_name='SPAIN' AND customer.email LIKE '%.es';
Note that for demonstration purposes I've aliased the join tables and
that (unfortunately) you can't alias the update table.
erik jones <erik@myemma.com>
software development
emma(r)
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: erik@myemma.com, lists.fabian@e-lumo.com
Subject: Re: UPDATE with WHERE clause on joined table
In-Reply-To: <44CA9E18.8020306@myemma.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