Oracle merge into介绍

 

在Oracle数据库常用操作中，有的时候需要使用B表来更新A表中的内容，一种方法使用update关联两表进行更新，还有一种办法即使用merge into。

 

merge into 一条语句可以实现update和insert的功能，并且效率较高

语法

 

MERGE INTO [your table-name] [rename your table here]

USING ( [write your query here] )[rename your query-sql and using just like a table]

ON ([conditional expression here] AND [...]...)

WHEN MATHED THEN [here you can execute some update sql or something else ]

WHEN NOT MATHED THEN [execute something else here ! ]

 

 

举例 

建立表test_sj_salary，插入示例数据

 

create table test_sj_salary
(
   id integer primary key,
   name varchar2(100),
   salary integer
);

truncate table test_sj_salary;

insert into test_sj_salary (ID, NAME, SALARY)
values (1, '张三', 5000);

insert into test_sj_salary (ID, NAME, SALARY)
values (2, '李四', 4000);

insert into test_sj_salary (ID, NAME, SALARY)
values (3, '王五', 6000);

insert into test_sj_salary (ID, NAME, SALARY)
values (4, '李六', 3500);

commit;

 

建立test_sj_cp，插入示例数据

create table test_sj_cp
(   
   id integer primary key,   
   name varchar2(100),   
   salary integer  
); 

truncate table test_sj_cp;

insert into test_sj_cp (ID, NAME, SALARY)   
values (9, '张三', 9000);   
  
insert into test_sj_cp (ID, NAME, SALARY)   
values (10, '李四', 10000);   
  
insert into test_sj_cp (ID, NAME, SALARY)   
values (11, '王二麻子', 11000); 

commit;

 

需要实现的功能：使用test_sj_cp根据相同name更新test_sj_salary中的salary字段

 

方法一，采用普通的update方法

 

update test_sj_salary a
set a.salary=(select b.salary from test_sj_cp b where b.name=a.name)
where exists (select 1 from test_sj_cp c where c.name=a.name);
commit;

 

方法二，采用merge into方法

merge into test_sj_salary a
using test_sj_cp b
  on (a.name=b.name)
when matched then update set a.salary=b.salary;
commit;

 是不是很简洁！

 

扩展功能，通过merge实现匹配到的更新，匹配不到的插入数据

 

merge into test_sj_salary a
using test_sj_cp b
  on (a.name=b.name)
when matched then update set a.salary=b.salary
when not matched then insert(id,name,salary) values(b.id,b.name,b.salary);
 
commit;