Re: UPDATE of several columns using SELECT statement

On Tue, Mar 17, 2009 at 03:55:47PM +0100, adam.slachta wrote:
> 1. When trying to update two columns
>
> UPDATE myFirstTable SET (fistCol, secCol) = (  SELECT anotherFistCol, anotherSecondCol FROM mySecondTable )

The more common say of expressing this would be something like:

  UPDATE myFirstTable a SET
      fistCol = b.anotherFistCol,
      secCol  = b.anotherSecondCol
    FROM mySecondTable b
    WHERE a.expr = b.expr;

> 2. When changed to (only the parentheses are changed):
>
> UPDATE myFirstTable SET (fistCol, secCol) = ((SELECT anotherFistCol, anotherSecondCol FROM mySecondTable ))
>
> I am getting: ERROR:  number of columns does not match number of values

PG is somewhat ad-hoc with its support of its record syntax and
unfortunately doesn't support the above at the moment.  The error is
coming from very early on (I think maybe when parsing) and I don't think
it ever gets as far as checking that the value coming back from the
select is a record of the same structure as is on the left hand side.

--
  Sam  http://samason.me.uk/