Mime-Version: 1.0 (Apple Message framework v752.2)
Content-Transfer-Encoding: 7bit
Message-Id: <B45EBA38-2DFC-45E7-921B-16D52A986E7A@e-lumo.com>
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
To: pgsql-sql@postgresql.org
From: Fabian Peters <lists.fabian@e-lumo.com>
Subject: UPDATE with WHERE clause on joined table
Date: Fri, 28 Jul 2006 23:24:56 +0200

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');