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 aset 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 ausing 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 ausing test_sj_cp b  on (a.name=b.name)when matched then update set a.salary=b.salarywhen not matched then insert(id,name,salary) values(b.id,b.name,b.salary); commit;