Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.oracle -> Re: Help on Oracle Update statement

Re: Help on Oracle Update statement

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 29 Apr 2004 17:42:19 -0700
Message-ID: <2687bb95.0404291642.1ceb3363@posting.google.com>


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 Thu Apr 29 2004 - 19:42:19 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US