dsriva schrieb:

In most cases a bad idea (IMHO)
but am not able to convert the following to do so.

Seems me too...
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_100
07.htm#i2112182

Best regards

Maxim

Comments embedded.

You can't always get what you want.

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.

David Fitzjarrell

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',117','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 --

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

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