The Oracle (tm) Users' Co-Operative FAQ

How do I replace the data of the “Target” Table with the data of the “Source” Table in a SQL Statement?


Author's name: Frank Zhou

Author's Email:zhou328@comcast.net

Date written: 3  February 2007

Oracle version(s): 10.2

How do I replace the data of the “Target” Table with the data of the “Source” Table in a SQL Statement?

Back to index of questions


The following SQL pattern can be used to replace the data of the “Target” Table with the data of the “Source” Table in a SQL Statement.

 

create table target as

select 1 as num, 'will_be_updated' as ch from dual

union all

select 2 as num, 'will_be_updated' as ch from dual

union all

select 20 as num,'will_be_deleted' as ch from dual

 

 

create table source as

SELECT level num, chr(ascii('A')+level-1) ch

from dual connect by level <= 5

 

SQL> select * from target;

 

       NUM CH                                                                   

---------- ---------------                                                     

         1 will_be_updated                                                     

         2 will_be_updated                                                      

        20 will_be_deleted                                                     

 

SQL> select * from source;

 

       NUM C                                                                   

---------- -                                                                    

         1 A                                                                   

         2 B                                                                   

         3 C                                                                    

         4 D                                                                   

         5 E          

MERGE INTO target tgt

USING 

(SELECT src.num, src.ch, tgt.num as tgt_num, tgt.ch as tg_ch

  FROM source  src FULL OUTER JOIN  target tgt

    ON (src.num = tgt.num )                          

) src

ON ( src.tgt_num  = tgt.num )

WHEN MATCHED

THEN

   UPDATE SET tgt.ch =  src.ch

   WHERE  NVL(tgt.ch, chr(0) ) != NVL(src.ch, chr(0))

   DELETE WHERE src.num IS NULL

WHEN NOT MATCHED

THEN

   INSERT ( tgt.num, tgt.ch)

   VALUES ( src.num, src.ch);

 

 

The “Target” Table is identical to the “Source” Table after execute the “Merge” SQL statement.

 

SQL> select * from target;

 

       NUM CH                                                                  

---------- ---------------                                                     

         1 A                                                                   

         2 B                                                                   

         3 C                                                                    

         4 D                                                                   

         5 E                                                                   

 

SQL> spool off;

 

 

 


 

Back to top

Back to index of questions