Oracle Server - UPDATE problem - FROM Clause not supported in Oracle

Author

UPDATE problem - FROM Clause not supported in Oracle

dsriva

2005-07-26, 9:23 am

Hi,
I want to have a single statement to work on both sql server and
oracle, but am not able to convert the following to do so.
I am trying to update a column

UPDATE field_char f SET maxLength = 254

FROM field_char fc, arschema s

WHERE f.fieldid IN('2', '4', '5', '101', '105', '117', '118')

AND (s.schemaId = fc.schemaId)

AND (s.schemaType = 1)

But it seems that the FROM clause is not supported in Oracle,

Thanks for any help
dsriva

Maxim Demenko

2005-07-26, 11:23 am

dsriva schrieb:
> Hi,
> I want to have a single statement to work on both sql server and
> oracle,
In most cases a bad idea (IMHO)
but am not able to convert the following to do so.
> I am trying to update a column
>
> UPDATE field_char f SET maxLength = 254
>
> FROM field_char fc, arschema s
>
> WHERE f.fieldid IN('2', '4', '5', '101', '105', '117', '118')
>
> AND (s.schemaId = fc.schemaId)
>
> AND (s.schemaType = 1)
>
>
>
> But it seems that the FROM clause is not supported in Oracle,
>
> Thanks for any help
> dsriva
>

Seems me too...
http://download-uk.oracle.com/docs/...07.htm#i2112182

Best regards

Maxim

fitzjarrell@cox.net

2005-07-26, 11:23 am

Comments embedded.
dsriva wrote:
> Hi,
> I want to have a single statement to work on both sql server and
> oracle,

You can't always get what you want.

> but am not able to convert the following to do so.
> I am trying to update a column
>
> UPDATE field_char f SET maxLength = 254
>
> FROM field_char fc, arschema s
>
> WHERE f.fieldid IN('2', '4', '5', '101', '105', '117', '118')
>
> AND (s.schemaId = fc.schemaId)
>
> AND (s.schemaType = 1)
>
>
>
> But it seems that the FROM clause is not supported in Oracle,
>

Possibly because it's a different DBMS engine? SQL Server implements
UPDATE one way, Oracle another. Get used to such things, as SQL isn't
SQL just because it's SQL. Each vendor decides what and how to
implement functionality from the SQL standard and no two vendors decide
on exactly the same features or the same methodology:

SQL Server:

select top 10 userId from user_access where accessLevel = 20

Oracle:

select a.userid from (select rownum r, userid from user_access where
accesslevel = 20) where r < 11;

Stop trying to make SQL Server into Oracle and Oracle into SQL Server.
All you'll end up with in return is a migraine, an ulcer and code that
isn't optimised for any DBMS.

> Thanks for any help
> dsriva

David Fitzjarrell

Mark D Powell

2005-07-26, 8:23 pm

Dsriva, I do not believe that the FROM clause is part of the ANSI
standard for an UPDATE statement. Regardless, I believe you want
something like the following:

update field_char f
set maxLength = 254
where f.fieldid in ('2','4','5','101',1
17','118')
and exists (select s.schemald
from arschema s
where s.schemald = f.schemald
and s.schema_type = 1)

You might want to try this version in SQL Server.

HTH -- Mark D Powell --

David Portas

2005-07-27, 8:23 pm

"dsriva" <dsrivast@gmail.com> wrote in message
news:1122389612.926329.57260@o13g2000cwo.googlegroups.com...
> Hi,
> I want to have a single statement to work on both sql server and
> oracle, but am not able to convert the following to do so.
> I am trying to update a column
>
> UPDATE field_char f SET maxLength = 254
>
> FROM field_char fc, arschema s
>
> WHERE f.fieldid IN('2', '4', '5', '101', '105', '117', '118')
>
> AND (s.schemaId = fc.schemaId)
>
> AND (s.schemaType = 1)
>
>
>
> But it seems that the FROM clause is not supported in Oracle,
>
> Thanks for any help
> dsriva
>

If you need to work across platforms then avoid propritary features. The
standard SQL update statement doesn't have a FROM clause or aliases for the
target table. The following tested on SQL2000.

UPDATE field_char
SET maxlength = 254
WHERE fieldid IN ('2', '4', '5', '101', '105', '117', '118')
AND EXISTS
(SELECT *
FROM arschema AS S
WHERE S.schemaid = field_char.schemaid
AND S.schematype = 1) ;

--
David Portas
SQL Server MVP
--

Sybrand Bakker

2005-07-27, 8:23 pm

On Wed, 27 Jul 2005 23:18:05 +0100, "David Portas"
< REMOVE_BEFORE_REPLYI
NG_dportas@acm.org> wrote:

>If you need to work across platforms then avoid propritary features.

If you want to get a working application under Oracle, ignore this
advice.

--
Sybrand Bakker, Senior Oracle DBA

David Portas

2005-07-29, 1:23 pm

"Sybrand Bakker" <postbus@sybrandb.demon.nl> wrote in message
news:7h3ge11ql426688
0c8ka450bd22snve7ua@
4ax.com...
> On Wed, 27 Jul 2005 23:18:05 +0100, "David Portas"
> < REMOVE_BEFORE_REPLYI
NG_dportas@acm.org> wrote:
>
>
> If you want to get a working application under Oracle, ignore this
> advice.
>
>
> --
> Sybrand Bakker, Senior Oracle DBA

You mean Oracle can't support Standard SQL92? I accept that you'll need
proprietary features in the physical implementation and for procedural code
but surely straight DML ought to be portable.

--
David Portas
SQL Server MVP
--

David Portas

2005-07-29, 1:23 pm

Sybrand Bakker

2005-07-29, 8:23 pm

On Fri, 29 Jul 2005 18:39:05 +0100, "David Portas"
< REMOVE_BEFORE_REPLYI
NG_dportas@acm.org> wrote:

>You mean Oracle can't support Standard SQL92? I accept that you'll need
>proprietary features in the physical implementation and for procedural code
>but surely straight DML ought to be portable.

I mean people like you seem to think SQL server is the standard, and
SQL server code will run unaltered on Oracle. Do you know how many
sites suffer from that assumption?
Do you know how many DBAs tear their hair out because that assumption
from the Evil Empire aka Microsoft?

Oracle != Sqlserver.

As for the standard, usually there are several levels in the standard.
Oracle usually complies to the entry level.

--
Sybrand Bakker, Senior Oracle DBA

David Portas

2005-07-30, 7:23 am

"Sybrand Bakker" <postbus@sybrandb.demon.nl> wrote in message
news:dj0le1tseurbgms
5cfl06gb6j4m53uojhe@
4ax.com...
> On Fri, 29 Jul 2005 18:39:05 +0100, "David Portas"
> < REMOVE_BEFORE_REPLYI
NG_dportas@acm.org> wrote:
>
>
> I mean people like you seem to think SQL server is the standard, and
> SQL server code will run unaltered on Oracle. Do you know how many
> sites suffer from that assumption?
> Do you know how many DBAs tear their hair out because that assumption
> from the Evil Empire aka Microsoft?
>
> Oracle != Sqlserver.
>
> As for the standard, usually there are several levels in the standard.
> Oracle usually complies to the entry level.
>
>
> --
> Sybrand Bakker, Senior Oracle DBA

> I mean people like you seem to think SQL server is the standard, and
> SQL server code will run unaltered on Oracle.

I'm aware of the ways in which SQL Server differs from standard SQL. The
code I posted is compliant with entry level SQL-92.

--
David Portas
SQL Server MVP
--

Matthias Hoys

2005-07-30, 7:23 am

"dsriva" <dsrivast@gmail.com> wrote in message
news:1122389612.926329.57260@o13g2000cwo.googlegroups.com...
> Hi,
> I want to have a single statement to work on both sql server and
> oracle, but am not able to convert the following to do so.
> I am trying to update a column
>
> UPDATE field_char f SET maxLength = 254
>
> FROM field_char fc, arschema s
>
> WHERE f.fieldid IN('2', '4', '5', '101', '105', '117', '118')
>
> AND (s.schemaId = fc.schemaId)
>
> AND (s.schemaType = 1)
>
>
>
> But it seems that the FROM clause is not supported in Oracle,
>
> Thanks for any help
> dsriva
>

What's the point of doing this ? Are you developing a database-independent
application ? I would say : bad idea. You should use the optimal features
for both engines. Also, SQL features can change between versions. Database
independent development leads to performance and scalability problems. One
example : to create a table with sequence numbers instead of using
autonumbers (SQL Server) or sequences (Oracle).

Matthias

Sponsored Links