Re: join in an update statement

"Jeff Goodman" <JGoodman10_at_nyc.rr.com> wrote in message news:<6LWe8.35514$Ci6.6226336_at_typhoon.nyc.rr.com>...
> Can you do a join in an update statement. I only want to update one table,
> but I want to set values from table 1 to equal values from table 2. For
> example:
>
> update table1, table2
> set table1.quantity = table2.quantity
> where table1.key = table2.key
>
> The only way I can think to do it is to say:
>
> update table1
> set table1.quantity = (select table2.quantity where table1.key = table2.key)
>
> But if I have more columns I want to update, I would have to have a seperate
> subselect for each column, which could become pretty costly. I would also
> like to test it with a select clause to see what would be updated but with
> the second update, I cant think of a corresponding select.
>
> Thanks.
> Jeff

<QUOTE>But if I have more columns I want to update, I would have to have a seperate
> subselect for each column, which could become pretty costly
<END QUOTE>

NOT TRUE.
update table
set (column, ... ) = (select column, ... from othertable)

Also of course no version!
In 8i and higher you can update an inline query update
(select column1, column2
 from tablea, tableb
 where col_from_tablea = col_from_tableb) set column1 = column2

Please confirm in your sql reference manual.

Hth

Sybrand Bakker
Senior Oracle DBA Received on Wed Feb 27 2002 - 02:19:17 CST