Re: Update with multiple tables

Ian Dodds wrote:

> Hi,
>
> I have 2 database tables. I need to update a column in one table with that
> in another. Somehow I need to have a join in the update statement but I
> can't get it to work.
>
> Take the following SQL which works fine in SQLServer.
>
> update table1 set table1.col1 =
> table2.col1
> from table2, table3
> where table2.col2 = table1.col2 and table2.col3= table3.col1
> and (table2.col3= 'CNI' or table2.col3 = 'CN2' or table2.col3 ='CSS')
> and table3.col2= 'Y';
>
> If anyone has any ideas, it would be appreciated. the Oracle help seems to
> be poor when trying to find out how to do an update with multiple tables.
>
> Thanks
>
> Ian

My idea is that you need to stop banging out SQL Server code in Oracle unless you enjoy this kind of thing.

Get a book on Oracle and learn how to write update statements appropriate to the product you are using.

The syntax you are looking for is more something like:

UPDATE table1
SET col1 = (

   SELECT col1
  FROM table2
 WHERE ...) Daniel Morgan Received on Tue Jun 25 2002 - 10:54:23 CDT