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