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_aSET table_a.prio = CASE WHEN size >= 10 THEN 1 ELSE 2 ENDFROM 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
>