Re: UPDATE problem - FROM Clause not supported in Oracle

"dsriva" <dsrivast@xxxxxxxxx> wrote in message
news:1122389612.926329.57260@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> 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
--


.

Relevant Pages

  • Re: How top actually works
    ... So the next one shows that order by clause has affected the result set ... sort these N rows according to my order by clause. ... Ie. you are telling SQL Server ... This is much the same as with rownum in Oracle, ...
    (comp.databases.ms-sqlserver)
  • Re: Performance: Conditions in WHERE clause vs. conditions in INNER JOIN?
    ... inclusion of the test for NULLs in an outer join with the conditions ... The special notation in Oracle is basically ... I think SQL server tends to carry the "just give the users the ... join conditions in the WHERE clause. ...
    (comp.databases.ms-sqlserver)
  • Re: SELECT COUNT(*)
    ... select GetDatewithout the from clause. ... In Oracle, the from is always required so if you used their ... I'd guess SQL Server and Sybase implement ... >Mike Epprecht, Microsoft SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: intersect function
    ... I assume you are looking for similar INTERSECT clause of oracle. ... In SQL Server 2000 you can use EXISTS clause to do this, your query can be rewritten as: ...
    (microsoft.public.sqlserver.mseq)
  • UPDATE problem - FROM Clause not supported in Oracle
    ... I want to have a single statement to work on both sql server and ... But it seems that the FROM clause is not supported in Oracle, ... Prev by Date: ...
    (comp.databases.oracle.server)