Re: UPDATE problem - FROM Clause not supported in Oracle

"dsriva" <dsrivast_at_gmail.com> wrote in message news:1122389612.926329.57260_at_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
--