Monday 18 May 2009

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 ( =
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!


otebos said...

simple,but :
1. not working - there`s no column job i t1
2. you should add extra job to t2 and use
when not matched then
insert (id,name,job) values (,'none',t2.job)
to show full beauty of merge

keep on good work ;)

otebos said...

sorry, I`ve missed alter table ;)
still point 2 is important

Kubilay said...

Otebos, are you sure is not working? Are you using Oracle?

I have re-run the scripts and they are fine. Table t1 is not supposed to have the "job" column initially. You add it afterwards with the command as shown in the post "
alter table t1 add job varchar2(60);" and then you bring the data from t2 into t1 with the merge statement.

Anyway, I am glad you liked it. You are right I could have elaborated more and shown the beauty with the INSERT when data does not match, but I skipped that :-(

Thank you for commenting.