Thread: UPDATE with WHERE clause on joined table

UPDATE with WHERE clause on joined table

From

Fabian Peters

Date:

28 July 2006, 21:30:21

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

Re: UPDATE with WHERE clause on joined table

From

Erik Jones

Date:

28 July 2006, 23:30:51

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)

Re: [SOLVED] UPDATE with WHERE clause on joined table

From

Fabian Peters

Date:

29 July 2006, 08:40:02

Am 29.07.2006 um 01:30 schrieb Erik Jones:

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

Erik, thanks a lot! Works like a charm and is so much more concise  
than my attempts.

cheers

Fabian