Re: Weird ORACLE update statement

In article <1992Dec8.153620.9115_at_infonode.ingr.com>, mcintyre_at_infonode.ingr.com (John Scott Mcintyre) writes:
> I am using ORACLE and want to be able to update rows in table1 using
> columns from table2 when the two tables can be joined.
>
> The SQL statement I would like to perform would look like this:
>
> update table1 set table1.manufacturer=table2.manufact,
> set table1.pur_cost=table2.sale_cost
> where table1.inv_number=table2.inv_number;
>
> The problem is UPDATE apparently cannot handle multiple tables.
>
> I have also tried different variations of views but cannot
> get the view to contain the joined and unjoined rows.
>
> If anyone has any ideas I would really appreciate the help.

Hi Scott,

Try this statement

UPDATE table1

   SET (manufacturer,pur_cost)=(SELECT manufacturer,sale_cost

                                  FROM table2
                                 WHERE table1.inv_number=table2.inv_number)
 WHERE EXISTS (SELECT 1
                 FROM table2
                WHERE table1.inv_number=table2.inv_number)

kind regards

Mike

                                                                          ^
 Federal Ministry of Science and Research                               B | M
<-------------------------------------------------------------------------+---->
 Computer Center                                                        W | F
                                                                          |
 Klaus-Michael Hatzinger            mail: hatzinger_m_at_bmwf1f.bmwf.gv.at   |
 Bankgasse 1/209                   phone: 0043-222-53120/5188             |
 1014 Vienna, Austria                fax: 0043-222-53120/5155             V
================================================================================