oracle中merge into语句详解
来源:互联网 发布:满族饮食 知乎 编辑:程序博客网 时间:2024/06/11 04:18
merge into 语句是insert 与update语句的结合,可以同时实现update和insert的功能。
一、merge into语句的语法。
MERGE INTO schema. table alias
USING { schema. table | views | query} alias
ON {(condition) }
WHEN MATCHED THEN
UPDATE SET {clause}
WHEN NOT MATCHED THEN
INSERT VALUES {clause};
--解析
INTO 子句
用于指定你所update或者Insert目的表。
USING 子句
用于指定你要update或者Insert的记录的来源,它可能是一个表,视图,子查询。
ON Clause
用于目的表和源表(视图,子查询)的关联,如果匹配(或存在),则更新,否则插入。
merge_update_clause
用于写update语句
merge_insert_clause
用于写insert语句
二、merge 语句的各种用法练习
创建表及插入记录
create table t_B_info_aa
( id varchar2(20),
name varchar2(50),
type varchar2(30),
price number
);
insert into t_B_info_aa values('01','冰箱','家电',2000);
insert into t_B_info_aa values('02','洗衣机','家电',1500);
insert into t_B_info_aa values('03','热水器','家电',1000);
insert into t_B_info_aa values('04','净水机','家电',1450);
insert into t_B_info_aa values('05','燃气灶','家电',800);
insert into t_B_info_aa values('06','太阳能','家电',1200);
insert into t_B_info_aa values('07','西红柿','食品',1.5);
insert into t_B_info_aa values('08','黄瓜','食品',3);
insert into t_B_info_aa values('09','菠菜','食品',4);
insert into t_B_info_aa values('10','香菇','食品',9);
insert into t_B_info_aa values('11','韭菜','食品',2);
insert into t_B_info_aa values('12','白菜','食品',1.2);
insert into t_B_info_aa values('13','芹菜','食品',2.1);
create table t_B_info_bb
( id varchar2(20),
type varchar2(50),
price number
);
insert into t_B_info_bb values('01','家电',2000);
insert into t_B_info_bb values('02','家电',1000);
1) update和insert同时使用
merge into t_B_info_bb b
using t_B_info_aa a --如果是子查询要用括号括起来
on (a.id = b.id and a.type = b.type) --关联条件要用括号括起来
when matched then
update set b.price = a.price --update 后面直接跟set语句
when not matched then
insert (id, type, price) values (a.id, a.type, a.price) --insert 后面不加into
---这条语句根据t_B_info_aa 更新了t_B_info_bb中的一条记录,插入了11条记录
2)只插入不更新
--处理表中数据,使表中的一条数据发生变化,删掉一部分数据。用来验证只插入不更新的功能
update t_B_info_bb b set b.price=1000 where b.id='02';
delete from t_B_info_bb b where b.type='食品';
--只是去掉了when matched then update语句
merge into t_B_info_bb b
using t_B_info_aa a
on (a.id = b.id and a.type = b.type)
when not matched then
insert (id, type, price) values (a.id, a.type, a.price)
3)只更新不插入
--处理表中数据,删掉一部分数据,用来验证只更新不插入
delete from t_B_info_bb b where b.type='食品';
--只更新不插入,只是去掉了when not matched then insert语句
merge into t_B_info_bb b
using t_B_info_aa a
on (a.id = b.id and a.type = b.type)
when matched then
update set b.price = a.price
三、加入限制条件的操作
变更表中数据,便于练习
update t_B_info_bb b
set b.price = 1000
where b.id='02'
delete from t_b_info_bb b where b.id not in ('01','02') and b.type='家电'
update t_B_info_bb b
set b.price = 8
where b.id='10'
delete from t_b_info_bb b where b.id in ('11','12','13') and b.type='食品'
表中数据
执行merge语句:脚本一和脚本二执行结果相同
脚本一: merge into t_B_info_bb b
using t_B_info_aa a
on (a.id = b.id and a.type = b.type)
when matched then
update set b.price = a.price where a.type = '家电'
when not matched then
insert
(id, type, price)
values
(a.id, a.type, a.price) where a.type = '家电';
----------------------
脚本二: merge into t_B_info_bb b
using (select a.id, a.type, a.price
from t_B_info_aa a
where a.type = '家电') a
on (a.id = b.id and a.type = b.type)
when matched then
update set b.price = a.price
when not matched then
insert (id, type, price) values (a.id, a.type, a.price);
结果:
上面两个语句是只对类型是家电的语句进行插入和更新
而脚本三是只对家电进行更新,其余的全部进行插入
merge into t_B_info_bb b
using t_B_info_aa a
on (a.id = b.id and a.type = b.type)
when matched then
update set b.price = a.price
where a.type = '家电'
when not matched then
insert
(id, type, price)
values
(a.id, a.type, a.price);
四、加删除操作
update子句后面可以跟delete子句来去掉一些不需要的行
delete只能和update配合,从而达到删除满足where条件的子句的记录
例句:
merge into t_B_info_bb b
using t_B_info_aa a
on (a.id=b.id and a.type = b.type)
when matched then
update set b.price=a.price
delete where (a.type='食品')
when not matched then
insert values (a.id, a.type, a.price) where a.id = '10'
- oracle中merge into语句详解
- Oracle中MERGE INTO语句
- Oracle中MERGE INTO语句的使用方法
- oracle merge into 用法详解
- oracle merge into 用法详解
- oracle merge into 用法详解
- oracle merge into 用法详解
- oracle merge into 用法详解
- oracle merge into 用法详解
- oracle merge into 用法详解
- oracle merge into 用法详解 .
- oracle merge into 用法详解
- Oracle Merge into 用法详解
- oracle merge into 用法详解
- oracle merge into 用法详解
- Oracle-Merge into使用详解
- Oracle的Merge into详解
- oracle 中merge into用法
- 《深入浅出nodejs》异步i/o笔记(1)
- svn不能打开 系统找不到
- Http请求中Content-Type讲解以及在Spring MVC注解中produce和consumes配置详解
- 欢迎使用CSDN-markdown编辑器
- file_get_contents failed to open stream: Permission denied in xxx解决办法
- oracle中merge into语句详解
- c++ 字符串流 sstream(常用于格式转换)
- OpenCV操作矩阵
- 使用ambari扩展hdfs节点
- Unity中的协同与线程
- MyBatis之resultMap的作用和xml配置折腾
- MySQL自增主键归零的方法
- 使用json所需的jar包
- 人工智能之思