Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: UPDATE FROM... Oracle vs Microsoft Conversion
On Fri, 30 Aug 2002 18:52:07 GMT, Daniel Morgan
<dmorgan_at_exesolutions.com> wrote:
>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
>
Great Thanks, Dan.
Received on Fri Aug 30 2002 - 18:42:27 CDT