pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUPDATE with WHERE clause on joined table
3+ messages / 2 participants
[nested] [flat]
* UPDATE with WHERE clause on joined table
@ 2006-07-28 21:24 Fabian Peters <lists.fabian@e-lumo.com>
2006-07-28 23:30 ` Re: UPDATE with WHERE clause on joined table Erik Jones <erik@myemma.com>
0 siblings, 1 reply; 3+ messages in thread
From: Fabian Peters @ 2006-07-28 21:24 UTC (permalink / raw)
To: pgsql-sql
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');
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: UPDATE with WHERE clause on joined table
2006-07-28 21:24 UPDATE with WHERE clause on joined table Fabian Peters <lists.fabian@e-lumo.com>
@ 2006-07-28 23:30 ` Erik Jones <erik@myemma.com>
2006-07-29 08:38 ` Re: [SOLVED] UPDATE with WHERE clause on joined table Fabian Peters <lists.fabian@e-lumo.com>
0 siblings, 1 reply; 3+ messages in thread
From: Erik Jones @ 2006-07-28 23:30 UTC (permalink / raw)
To: Fabian Peters <lists.fabian@e-lumo.com>; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SOLVED] UPDATE with WHERE clause on joined table
2006-07-28 21:24 UPDATE with WHERE clause on joined table Fabian Peters <lists.fabian@e-lumo.com>
2006-07-28 23:30 ` Re: UPDATE with WHERE clause on joined table Erik Jones <erik@myemma.com>
@ 2006-07-29 08:38 ` Fabian Peters <lists.fabian@e-lumo.com>
0 siblings, 0 replies; 3+ messages in thread
From: Fabian Peters @ 2006-07-29 08:38 UTC (permalink / raw)
To: Erik Jones <erik@myemma.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-07-29 08:38 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-28 21:24 UPDATE with WHERE clause on joined table Fabian Peters <lists.fabian@e-lumo.com>
2006-07-28 23:30 ` Erik Jones <erik@myemma.com>
2006-07-29 08:38 ` Fabian Peters <lists.fabian@e-lumo.com>
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