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 <[EMAIL PROTECTED]>
software development
emma(r)
Erik, thanks a lot! Works like a charm and is so much more concise
than my attempts.
cheers
Fabian
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
