Re: UPDATE TABLE WITH JOIN

Hi

The better way is to do it with
a subquery, in the example you give
you only need one subquery
you know that you can link table from the update in the subselect

like update table1 t1
set c1 = (select c1 from table2 t2

          where t1.id = t2.id)

hth
Chris

toqeers_at_hotmail.com (toqeer) wrote in message news:<b6ab8a8e.0201221031.753fe23d_at_posting.google.com>...
> The query like this, works fine in Microsoft SQL SERVER
>
> UPDATE TABLE table1 t1 SET
> t1.col1 = t2.col1,
> t1.col2 = t2.col2
> t1.col3 = t2.col3
> FROM table2 t2, table t3
> WHERE t1.col1 = t2.col1
> AND t2.col2 = t3.col1
> etc...
> The main idea is to update a table with values in other one or more
> tables by joining the tables.
> AS FAR AS I KNOW
> ORACLE doesn't support FROM clause in UPDATE statement (like I used in
> above example). I can work around If I have fixed values to update the
> table.
> Or other way is to use a subquery (SELECT col1 from ....) with join
> condition for each column to update.
> I want to know is there any better way to do that (IN ORACLE), I want
> to avoid many subqueries.
> If any one knows please help.
>
> Regards.
> Toqeer Sardar
Received on Wed Jan 23 2002 - 17:14:00 CET