Re: common UPDATE syntax for SqlServer and Oracle

On Fri, 26 Nov 2004 11:01:39 +0100, Jan van Veldhuizen wrote:

>Thanks. I'm going to test that.
>
>That syntax will work with one column to be updated.
>What if I have to columns?
>
>I think the oracle sql will support something like:
>UPDATE Table1
> SET (city_id, another_column) =
> (SELECT T2.id, other_column FROM etctera...
>
>But that no standard SqlServer syntax as far as I know.

Hi Jan,

That's right. Using ANSI-standard SQL, the only way to update multiple columns with values from another table is to repeat the subquery:

UPDATE Table1
SET city_id = (SELECT T2.id FROM etcetera...)

     , another_column = (SELECT other_column FROM etcetera...) WHERE .... Best, Hugo

-- 

(Remove _NO_ and _SPAM_ to get my e-mail address)