merge into的模板使用

1，创建两测试表

create table merge_test1 (test1_id number,test1_name varchar2(100));

create table merge_test2 (test2_id number,test2_name varchar2(100));
2，测试表插入数据
insert into merge_test1(test1_id,test1_name) values (1,'a');
insert into merge_test1(test1_id,test1_name) values (2,'b');
insert into merge_test1(test1_id,test1_name) values (3,'c');

insert into merge_test2(test2_id,test2_name) values (2,'e');
insert into merge_test2(test2_id,test2_name) values (3,'f');

3，查询两测试表
select * from merge_test1;

select * from merge_test2;

 

4，现在要实现更新merge_test1的与merge_test2对应的值
merge into merge_test1 t1
using (select * from merge_test2) t2 --using里的集合也可以是多个表生成的集合
on (t1.test1_id=t2.test2_id)
when matched then update set t1.test1_name=t2.test2_name --更新test1_id值一样的行
delete where t1.test1_id=t2.test2_id--删除test1_id值一样的行
when not matched then insert (t1.test1_id,t1.test1_name) values (5,'insert');--没匹配上的插入一条新的数据