Thread: UPDATE table SET col = (SELECT ...)

UPDATE table SET col = (SELECT ...)

From
"Vangelis-Maria Tougia"
Date:
Hi,
I found follwing email sent to you in internet.
I am interested in same issue myself.
Did you finally found out any more information about how to Update a column of a table by selecting value from another table?
 
 
Hello,

If I understand the SQL references I have read, UPDATEs are allowed to
pull data from SELECT statements, something like

  UPDATE table SET col1 = (SELECT val1 FROM table WHERE id = 34),
               SET col2 = (SELECT val2 FROM table WHERE id = 34)
         WHERE id = 35;

However, the PostgreSQL parser chokes on the 'SELECT'. The
documentation says

  UPDATE table SET column = expression [, ...]
      [ FROM fromlist ]
      [ WHERE condition ]        

  expression = A valid expression or value to assign to column.


I don't know exactly how to interpret `valid expression'.
     
--
Eric Marsden
emarsden @ mail.dotcom.fr
It's elephants all the way down
 

Re: UPDATE table SET col = (SELECT ...)

From
Ian Barwick
Date:
On Wednesday 25 June 2003 11:00, Vangelis-Maria Tougia wrote:
> Hi,
> I found follwing email sent to you in internet.
> I am interested in same issue myself.
> Did you finally found out any more information about how to Update a column
> of a table by selecting value from another table?

Not sure if this has been answered previously, but here goes:

> Hello,
>
> If I understand the SQL references I have read, UPDATEs are allowed to
> pull data from SELECT statements, something like
>
>   UPDATE table SET col1 = (SELECT val1 FROM table WHERE id = 34),
>                SET col2 = (SELECT val2 FROM table WHERE id = 34)                ^^^
>          WHERE id = 35;

The second "SET" is not necessary or allowed and is causing the update to
fail.


Ian Barwick
barwick@gmx.net