Update multiple columns with select statement?

Поиск
Список
Период
Сортировка
От John Sidney-Woollett
Тема Update multiple columns with select statement?
Дата
Msg-id 1407.192.168.0.64.1071074296.squirrel@mercury.wardbrook.com
обсуждение исходный текст
Ответ на Re: highest match in group  (Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl>)
Список pgsql-general
Is it possible to update multiple columns of a table using a select
statement to derive the values?

For example (trying to port from Oracle to Postgres)

-- update the image record for original size image
update WPImage set (WPImageStateID, Width, Height, ContentType,
ContentLength) = (
  select 3, Width, Height, ContentType, ContentLength
  from WPImageHeader
  where WDResourceID = pResourceID
)
where WDResourceID = pResourceID and WPSizeTypeID = 0;

I have seen mention of a Postgres (specific) feature,

   update ... set .... from {other_table} where {join_condition}


Is this the most appropriate way to do the above in postgres?

update WPImage
set WPImageStateID =  3,
Width = WPImageHeader.Width,
Height = WPImageHeader.Height,
ContentType = WPImageHeader.ContentType,
ContentLength = WPImageHeader.ContentLength
where WPImage.WDResourceID = WPImageHeader.WDResourceID
and WPImage.WDResourceID = pResourceID
and WPImage.WPSizeTypeID = 0;

{where pResourceID is a variable}

Thanks

John Sidney-Woollett

В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: highest match in group
Следующее
От: "Hisham Al-Shurafa"
Дата:
Сообщение: Disabling or forwarding external connections