common UPDATE syntax for SqlServer and Oracle

[Jan van Veldhuizen]

The UPDATE table FROM syntax is not supported by Oracle.

I am looking for a syntax that is understood by both Oracle and SqlServer.

Example:

Table1:

id name city city_id
1 john newyork null
2 peter london null
3 hans newyork null

Table2:

id city
23 london
24 paris
25 newyork

UPDATE table1
SET city_id = table2.id
FROM table1, table2
WHERE table1.city = Table2.city

If possible I do not want to have two different statements for Oracle and
SqlServer

Please do not tell me that these tables are not normalized, it's just an
example!

Thanks for any hints.

Jan van Veldhuizen

[David Portas]

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
--

[Jan van Veldhuizen]

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" <RE****************************@acm.org> wrote in message
news:qo********************@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
--

[Hugo Kornelis]

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)

[Serge Rielau]

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

[Hugo Kornelis]

On Fri, 26 Nov 2004 07:31:59 -0500, Serge Rielau wrote:

I believe the ANSI standard allows:
UPDATE Table1
SET (city_id, another_column) = (SELECT T2.id, other column FROM etc
WHERE ...)
WHERE EXISTS(...)

Hi Serge,

Umm, yes. I believe you're right. Unfortunately, that part of ANSI sql is
not available in SQL Server 2000 (don't know about Oracle, thoug), so it
won't help Jan.

Best, Hugo
--

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

[Jarl Hermansson]

"Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message news:<41***********************@news.xs4all.nl>...

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.

Jan,

The multi-column UPDATE you describe above is actually included in the
SQL-2003 standard. (The non-Core feauture T641 - "Multiple column
assignment")
Regards,
Jarl

[DA Morgan]

Hugo Kornelis wrote:

On Fri, 26 Nov 2004 07:31:59 -0500, Serge Rielau wrote:

I believe the ANSI standard allows:
UPDATE Table1
SET (city_id, another_column) = (SELECT T2.id, other column FROM etc
WHERE ...)
WHERE EXISTS(...)

Hi Serge,

Umm, yes. I believe you're right. Unfortunately, that part of ANSI sql is
not available in SQL Server 2000 (don't know about Oracle, thoug), so it
won't help Jan.

Best, Hugo

It does exist in Oracle. Too bad about SQL Server though.
--
Daniel A. Morgan
University of Washington
da******@x.washington.edu
(replace 'x' with 'u' to respond)