Update multiple columns with select statement?

From: "John Sidney-Woollett" <johnsw(at)wardbrook(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Update multiple columns with select statement?
Date: 2003-12-10 16:38:16
Message-ID: 1407.192.168.0.64.1071074296.squirrel@mercury.wardbrook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Hisham Al-Shurafa 2003-12-10 16:50:25 Disabling or forwarding external connections
Previous Message Tom Lane 2003-12-10 16:37:11 Re: highest match in group