一、创建表
create table test1
(id number primary key,
name varchar2(50));
create table test2
(id number primary key,
name varchar2(50));
二、插入数据
insert into test1(id) values(1);
insert into test2(id,name) values(1,'王三');
insert into test2(id,name) values(2,'朱二');
commit;
三、使用merge into 使用update方式
SQL> MERGE INTO test1 T1
2 USING test2 T2
3 ON (T1.id = T2.id)
4 WHEN MATCHED THEN
5 UPDATE SET T1.NAME = t2.name
6 ;
1 row merged
SQL> select * from test1;
ID NAME
---------- --------------------------------------------------
1 王三
四、使用merge into 使用update与insert into方式
SQL> MERGE INTO test1 T1
2 USING test2 T2
3 ON (T1.id = T2.id)
4 WHEN MATCHED THEN
5 UPDATE SET T1.NAME = t2.name
6 WHEN NOT MATCHED THEN
7 INSERT (id,name) VALUES(t2.id,t2.name);
2 rows merged
SQL> select * from test2;
ID NAME
---------- --------------------------------------------------
1 王三
2 朱二
注:实现成功,是一对比较好的处理方式,此时需要关联条件是唯一性
五、merge into 除了等值条件外的另外一个条件
SQL> MERGE INTO test1 T1
2 USING test2 T2
3 ON (T1.id = T2.id and T2.id=2)
4 WHEN MATCHED THEN
5 UPDATE SET T1.NAME = t2.name
6 ;
0 rows merged
SQL>
SQL> MERGE INTO test1 T1
2 USING test2 T2
3 ON (T1.id = T2.id and T2.id=1)
4 WHEN MATCHED THEN
5 UPDATE SET T1.NAME = t2.name
6 ;
1 row merged
SQL> select * from test1;
ID NAME
---------- --------------------------------------------------
1 王三
注:条件都可以放在on后面
总结:
1 merge into语句条件均可以放入on中
2 根据唯一性是一个很好的处理字段的方法
3 一般需要保证MERGE INTO test1 T1中test1中的字段为唯一行
ON (T1.id = T2.id)