Re: UPDATE FROM... Oracle vs Microsoft Conversion

Sometimes it helps to put a legit email address. In the event the problem needs a more lengthy solution, it would avoid any back and forth responses that may end up cluttering the newsgroup.

Anyways, try this;

update t2 set(col1,col2,col3) =

( select col1,col2,col3 from t3 where col1=3)

where col1=3;

You can play around with the conditions, or none if not needed.

"Mike Dwyer" <abc_at_iom.com> wrote in message news:3d6fb89b.973199687_at_news.sf.sbcglobal.net...
> 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
>
Received on Fri Aug 30 2002 - 16:50:42 CDT