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 ?  (Pavel Stehule <pavel.stehule@gmail.com>)
Список 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 по дате отправления:

Предыдущее
От: Marcin Krawczyk
Дата:
Сообщение: Re: skip if latter value equal
Следующее
От: Pavel Stehule
Дата:
Сообщение: Re: How update a table within a join efficiently ?