From: Frank Bax <fbax@sympatico.ca>
To: pgsql-sql@postgresql.org
Subject: Re: update on join ?
Date: Wed, 21 Nov 2007 22:34:24 -0500
Message-ID: <4744F8C0.7040106@sympatico.ca> (raw)
In-Reply-To: <4744F06A.8080302@gmx.net>
References: <4744F06A.8080302@gmx.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: fbax@sympatico.ca
Subject: Re: update on join ?
In-Reply-To: <4744F8C0.7040106@sympatico.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox