Re: update on join ?

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