Re: common UPDATE syntax for SqlServer and Oracle
From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Fri, 26 Nov 2004 07:31:59 -0500
Message-ID: <30om2bF32jaegU2_at_uni-berlin.de>
>
>
> 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
I believe the ANSI standard allows:
UPDATE Table1
Date: Fri, 26 Nov 2004 07:31:59 -0500
Message-ID: <30om2bF32jaegU2_at_uni-berlin.de>
Hugo Kornelis wrote:
> 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
I believe the ANSI standard allows:
UPDATE Table1
SET (city_id, another_column) = (SELECT T2.id, other column FROM etc
WHERE ...)
WHERE EXISTS(...)
Cheers
Serge
Received on Fri Nov 26 2004 - 13:31:59 CET