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: 30 Apr 2004 16:39:04 -0700
Message-ID: <2687bb95.0404301539.1001feee@posting.google.com>


Don <don_leeNO_aa_SPAM_at_telus.net> wrote in message news:<dbs390538o5kcbl72q5ukasii0ebi6lvsf_at_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 --

Yes, you can nest subqueries including coordinated sub-queries though in my experience the second subquery would be coordinated to the first subquery which in turn is coordinated to the driving query. In your example you would probably be better off to combine the two subqueries into a join.

HTH -- Mark D Powell -- Received on Fri Apr 30 2004 - 18:39:04 CDT

Original text of this message

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