2009/7/10 Andreas <maps...@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 > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
