merge into测试案例
来源:互联网 发布:辨别产品真假软件 编辑:程序博客网 时间:2024/05/19 14:37
一、创建表
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)
- merge into测试案例
- oracle merge into 测试
- merge into
- merge into
- merge into
- merge into
- merge into
- Merge into
- merge into
- merge into
- Merge into
- merge into
- Merge into
- Merge Into
- Oracle select --merge into:
- merge into用法
- Merge into 详细介绍
- merge into的用法
- 房地产相关
- java发送qq邮件
- 可动态布局的Android抽屉之基础
- 用SecureCRT来上传和下载文件
- 中小企业迫切需要“云计算”应用
- merge into测试案例
- python服务器和客户端网络通讯UDP
- chmod命令
- 如何使用SSH连接Win7系统+VMware+Ubuntu
- tar命令
- proxy模式
- 《Java程序员上班那点事儿》自我小结1
- 【LeetCode OJ】Sum Root to Leaf Numbers
- !important