Re: common UPDATE syntax for SqlServer and Oracle

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.

"David Portas" <REMOVE_BEFORE_REPLYING_dportas_at_acm.org> wrote in message news:qoednbGWLZwC-TvcRVn-1A_at_giganews.com...
> The ANSI Standard syntax supported by both products is
>
> UPDATE Table1
> SET city_id =
> (SELECT T2.id
> FROM Table2 AS T2
> WHERE T2.city = Table1.city) ;
>
> Depending on requirements you may want to include a WHERE EXISTS
> (equivalent to the proprietary INNER JOIN syntax)
>
> UPDATE Table1
> SET city_id =
> (SELECT T2.id
> FROM Table2 AS T2
> WHERE T2.city = Table1.city)
> WHERE EXISTS
> (SELECT *
> FROM Table2 AS T2
> WHERE T2.city = Table1.city) ;
>
> --
> David Portas
> SQL Server MVP
> --
>
>
Received on Fri Nov 26 2004 - 04:01:39 CST