Re: UPDATE FROM... Oracle vs Microsoft Conversion

Mike Dwyer wrote:

> I'm converting from Microsoft SQL 2000 to Oracle 9i r2.
> This is a question about the differences in the UPDATE statement
> between the two dialects.
>
> In Microsoft, i had a table (mytable) with several columns that I
> periodically updated from corresponding columns from another table
> (anothertable). I used a form of the UPDATE command as such:
>
> UPDATE mytable
> set field1=u.field1, field2= u.field2......field20=u.field2
> FROM
> mytable join anothertable u
> ON mytable.primarykey= u.primarykey
>
> This form apparently doesn't work with Oracle 9i, R2.
> It appears that I would have to include a separate select statement
> for each and every one of the columns I want to update. Is this true?
> or is there another way. Mytable really has about 80 columns to
> update. That would make one hell of an SQL statement!
>
> Thanks.
> -Mike

UPDATE mytable
SET (field1, field2, field3, field4) = (

   SELECT field1, field2, field3, field4    FROM anothertable);

Daniel Morgan Received on Fri Aug 30 2002 - 13:52:07 CDT