Re: Update statment in Oracle

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 27 Aug 2001 07:31:07 -0700
Message-ID: <9mdljb031io@drn.newsguy.com>

In article <998905644.129215_at_tux2.ham.acs-gmbh.de>, "Adam says...
>
>I have two tables:
>table1 with fields "id" and "x"
>table2 with fields "id" and "x"
>
>I'd like to update value of field "x" from table1 with value of field "x"
>from table2 where "id" of table1 = "id" od table2. I've try this statement:
>
>UPDATE table1
>SET table1.x = table2.x
>WHERE table1.id = table.id;
>
>but I always receive error that the name of feld is not correct. How can
>write such easy statement? In Access I've used "left join" and so on without
>any problems...
>Help
>
>Greets,
>Adam
>

update T1

   set c1 = ( select c2 from T2 where T2.key = T1.key )  where exists ( select c2 from T2 where T2.key = T1.key ) /

is one method, if the proper keys are in place:

SQL> update
  2 ( select

  3            t1.y t1_y, t2.y t2_y
  4            from t1, t2
  5           where t1.x = t2.x )
  6     set t1_y = t2_y

  7 /

is another.

>-------------------------------------------
>Adam Boczek
>IT Consultant
>GFT Solutions GmbH
>adam.boczek_at_gft-solutions.de
>-------------------------------------------
>
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Mon Aug 27 2001 - 09:31:07 CDT