Re: How update a table within a join efficiently ?

2009/7/10 Andreas <maps(dot)on(at)gmx(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>