Re: Help on Oracle Update statement

Don <don_leeNO_aa_SPAM_at_telus.net> wrote in message news:<q0j290t74eannlodc70rn7ffenafudnpu8_at_4ax.com>...
> Hi,
>
> I am moving from Sybase to Oracle and I used to be able to do update
> statement like this in Sybase:
>
> UPDATE TABLE1
> SET T1.field1 = T2.field2
> FROM TABLE1 T1, TABLE2 T2
> WHERE T1.field2 = T2.field2
> AND ....
>
> but in Oracle it is not valid. Does anyone know how to convert it to
> Oracle?
>
> Thanks in advance..

One form is:
update table1 t1
set t1.field1 = ( select t2.field2

                  from table2 t2
                  where t2.field2 = t1.field1 ...
                 )
where exists ( select 'X' from table2 t3
               where t3.field2 = t1.field1 ...other cond ...
              );

The first subquery gets the value from the other table where the values match while the where clause on the update prevent updating the column to null for non-matching rows.

HTH -- Mark D Powell -- Received on Fri Apr 30 2004 - 02:42:19 CEST