Message-ID: <4744F8C0.7040106@sympatico.ca>
Date: Wed, 21 Nov 2007 22:34:24 -0500
From: Frank Bax <fbax@sympatico.ca>
User-Agent: Thunderbird 2.0.0.6 (X11/20070818)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: update on join ?
References: <4744F06A.8080302@gmx.net>
In-Reply-To: <4744F06A.8080302@gmx.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Andreas wrote:
> I'd like to update a table  efficiently  where the relevant select-info 
> is in another table that is foreign-linked.
> 
> Stupid example. 2 tables:
> things (thing_id integer, name varchar(100), color varchar(100))
> inventory (item_id integer, thing_fk integer references things 
> (thing_id), number)
> 
> For some reason I'd want to set the number of every red item to 0.
> This inventory doesn't contain the color but the foreign key to the 
> other table where the color is found.
> 
> I tried
> 
> UPDATE  things JOIN inventory ON things.thing_id = inventory.thing_fk
> SET number = 0
> WHERE color = 'red'
> 
> PSQL didn't like the JOIN though.
> It works like this:
> 
> UPDATE inventory
> SET number = 0
> WHERE  thing_fk IN (SELECT thing_id FROM things WHERE color = 'red')
> 
> It's effective but is it efficient, too?


What about:

UPDATE  things, inventory
   SET number = 0
   WHERE color = 'red'
   AND things.thing_id = inventory.thing_fk