Update one table from another using ORACLE MERGE INTO

Sometimes, during ETL or when is necessary to do data cleansing, I need to update a table with data from another table. I have been looking for scripts to do this easily and I have come across MERGE INTO. A very easy to use and understand SQL statement.


Assume we have 2 tables as:

create table t1 
(
id number primary key not null,
name varchar2(50)
);


create table t2
(
id number primary key not null,
job varchar2(50)
);

insert into t1 values (1, 'Kubilay');
insert into t1 values (2, 'Robin');
insert into t1 values (3, 'Kenny');

select * from t1;


ID                     NAME                                               
---------------------- -------------------------------------------------- 
1                      Kubilay                                            
2                      Robin                                              
3                      Kenny                                              

3 rows selected




insert into t2 values (1, 'DBA');
insert into t2 values (2, 'SA');
insert into t2 values (3, 'Developer');

select * from t2;


ID                     JOB                                                
---------------------- -------------------------------------------------- 
1                      DBA                                                
2                      SA                                                 
3                      Developer                                          

3 rows selected

And now we want to create a new column in t1 and move the data which is matching (primary key to primary key) from table t2 to table t1.

alter table t1 add job varchar2(60);

Use MERGE INTO to update table t1 with data from table t2.

merge into t1
using t2
on (t1.id = t2.id)
when matched
then update set 
t1.job = t2.job;


select * from t1;

ID                     NAME                                               JOB                                                          
---------------------- -------------------------------------------------- ------------------------------------------------------------ 
1                      Kubilay                                            DBA                                                          
2                      Robin                                              SA                                                           
3                      Kenny                                              Developer                                                    

3 rows selected

As you can see in the link you can use the MERGE statement to insert new rows like UPSERT when data in table t2 doesn't match with t1. That is, an Oracle UPSERT statement!