Thread: How update a table within a join efficiently ?

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?

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
>