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: Don <don_leeNO_aa_SPAM_at_telus.net>
Date: Fri, 30 Apr 2004 06:31:07 GMT
Message-ID: <dbs390538o5kcbl72q5ukasii0ebi6lvsf@4ax.com>

And yes, using alias/and subquery is working .. thanks for all who response quickly..

Now my next question is : would the alias works in multiple nest level? For example :

UPDATE TABLE1 T1
set T1.field1 = ( select T2.field1 from TABLE2 T2 where T2.field2 = (

        select T3.field3 from TABLE3 T3 where T3.field1 = T1.field1 )

Thanks again ...

Mark.Powell_at_eds.com (Mark D Powell) wrote:

>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 - 01:31:07 CDT

Original text of this message

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