From: don_leeNO_aa_SPAM@telus.net   
      
   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@eds.com (Mark D Powell) wrote:   
      
   >Don  wrote in message news:...   
   >> 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 --   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)