From: Pavel Stehule <pavel.stehule@gmail.com>
To: Andreas <maps.on@gmx.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: How update a table within a join efficiently ?
Date: Fri, 10 Jul 2009 10:21:55 +0200
Message-ID: <162867790907100121x4437c0ddj63d14524768ffc56@mail.gmail.com> (raw)
In-Reply-To: <4A56F6D2.7070802@gmx.net>
References: <4A56F6D2.7070802@gmx.net>
2009/7/10 Andreas <maps.on@gmx.net>:
> 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)
hello
don't repeat target table in FROM clause
UPDATE table_a
SET table_a.prio = CASE WHEN size >= 10 THEN 1 ELSE 2 END
FROM table_b WHERE table_a.table_a_id = table_b.table_a_id;
regards
Pavel Stehule
>
> 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?
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
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: pavel.stehule@gmail.com, maps.on@gmx.net
Subject: Re: How update a table within a join efficiently ?
In-Reply-To: <162867790907100121x4437c0ddj63d14524768ffc56@mail.gmail.com>
* 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