X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 82C599FB203 for ; Sat, 29 Jul 2006 05:40:01 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 43517-06 for ; Sat, 29 Jul 2006 05:39:42 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.e-lumo.com (waxaale.e-lumo.com [81.169.187.115]) by postgresql.org (Postfix) with ESMTP id 3189A9FB2AE for ; Sat, 29 Jul 2006 05:39:17 -0300 (ADT) Received: from [10.100.100.10] ([10.100.100.10]) by mail.e-lumo.com with esmtp; Sat, 29 Jul 2006 08:39:01 +0000 id 00033C0E.44CB1EA5.000075C1 In-Reply-To: <44CA9E18.8020306@myemma.com> References: <44CA9E18.8020306@myemma.com> Mime-Version: 1.0 (Apple Message framework v752.2) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: <7ECE6348-3F8E-4F34-BE48-D7F416EE6F46@e-lumo.com> Cc: pgsql-sql@postgresql.org Content-Transfer-Encoding: 7bit From: Fabian Peters Subject: Re: [SOLVED] UPDATE with WHERE clause on joined table Date: Sat, 29 Jul 2006 10:38:58 +0200 To: Erik Jones X-Mailer: Apple Mail (2.752.2) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.215 tagged_above=0 required=5 tests=AWL, SPF_HELO_SOFTFAIL, SPF_PASS X-Spam-Level: * X-Archive-Number: 200607/366 X-Sequence-Number: 25940 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 > software development > emma(r) Erik, thanks a lot! Works like a charm and is so much more concise than my attempts. cheers Fabian