469,270 Members | 1,733 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,270 developers. It's quick & easy.

common UPDATE syntax for SqlServer and Oracle

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


Jul 19 '05 #1
7 43733
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
--
Jul 19 '05 #2
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
--

Jul 19 '05 #3
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)
Jul 19 '05 #4
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
Jul 19 '05 #5
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)
Jul 19 '05 #6
"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
Jul 19 '05 #7
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)
Jul 19 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Dave | last post: by
4 posts views Thread by Karaoke Prince | last post: by
7 posts views Thread by Bart Torbert | last post: by
8 posts views Thread by Jan van Veldhuizen | last post: by
1 post views Thread by ralph_noble | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.