From: Andreas <maps.on@gmx.net>
To: pgsql-sql@postgresql.org
Subject: How update a table within a join efficiently ?
Date: Fri, 10 Jul 2009 10:07:46 +0200
Message-ID: <4A56F6D2.7070802@gmx.net> (raw)
Hi,
how would I update a table within a join in a more efficient way?
E.g. the folowing case:
table_a holds abstract elements. One column represents "priority" which
can be based on information of other tables.
table_b might hold such details in a column "size" for about 3000 of
80000 records out of table_a.
I'd like to do this:
UPDATE table_a
SET table_a.prio = CASE WHEN size >= 10 THEN 1 ELSE 2 END
FROM table_a JOIN table_b USING (table_a_id)
This doesn't work.
But the folowing does, though it looks not efficient with those 3000
SELECTs instead of one preparing JOIN that fetches the relevant info. :(
UPDATE table_a
SET prio =
(
SELECT CASE WHEN size >= 10 THEN 1 ELSE 2 END
FROM table_b
WHERE table_a.table_a_id = table_b.table_a_id
)
WHERE table_a_id IN (SELECT table_a_id FROM table_b);
Is there a better way?
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: maps.on@gmx.net
Subject: Re: How update a table within a join efficiently ?
In-Reply-To: <4A56F6D2.7070802@gmx.net>
* 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