pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
How update a table within a join efficiently ? 2+ messages / 2 participants [nested] [flat]
* How update a table within a join efficiently ? @ 2009-07-10 08:07 Andreas <maps.on@gmx.net> 0 siblings, 1 reply; 2+ messages in thread From: Andreas @ 2009-07-10 08:07 UTC (permalink / raw) To: pgsql-sql 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? ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: How update a table within a join efficiently ? @ 2009-07-10 08:21 Pavel Stehule <pavel.stehule@gmail.com> parent: Andreas <maps.on@gmx.net> 0 siblings, 0 replies; 2+ messages in thread From: Pavel Stehule @ 2009-07-10 08:21 UTC (permalink / raw) To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql 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 > ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2009-07-10 08:21 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2009-07-10 08:07 How update a table within a join efficiently ? Andreas <maps.on@gmx.net> 2009-07-10 08:21 ` Pavel Stehule <pavel.stehule@gmail.com>
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