How update a table within a join efficiently ?
| От | Andreas |
|---|---|
| Тема | How update a table within a join efficiently ? |
| Дата | |
| Msg-id | 4A56F6D2.7070802@gmx.net обсуждение исходный текст |
| Ответы |
Re: How update a table within a join efficiently ?
|
| Список | 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?
В списке pgsql-sql по дате отправления: