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
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 |