1. 语法
merge into 的语法如下所示:
MERGE INTO [target-table] T USING [source-table sql] S ON([conditional expression] and [...]...)
WHEN MATCHED
THEN [UPDATE sql]
WHEN NOT MATCHED
THEN [INSERT sql]
判断源表 S 和目标表 T 是否满足 ON 中的条件,如果满足则用 S 表去更新 T 表,如果不满足,则将 S 表数据插入 T 表中。但是有很多可选项,如下:
- 普通模式
- 只 update 或者只 insert
- 无条件 insert 实现
- 带 delete 的 update
2. 测试表
-- 目标表
create table target
(
id NUMBER not null,
name VARCHAR2(12) not null,
year NUMBER
);
-- 源表
create table source
(
id NUMBER not null,
aid NUMBER not null,
name VARCHAR2(12) not null,
year NUMBER,
city VARCHAR2(12)
);
-- 插入测试数据
insert into target values(1,'liuwei',20);
insert into target values(2,'zhangbin',21);
insert into target values(3,'fuguo',20);
insert into source values(1,2,'zhangbin',30,'吉林');
insert into source values(2,4,'yihe',33,'黑龙江');
insert into source values(3,3,'fuguo','','山东');
两表的的查询结果如下所示:
SQL> select * from target;
ID NAME YEAR
---------- ------------ ----------
1 liuwei 20
2 zhangbin 21
3 fuguo 20
SQL> select * from source;
ID AID NAME YEAR CITY
---------- ---------- ------------ ---------- ------------
1 2 zhangbin 30 吉林
2 4 yihe 33 黑龙江
3 3 fuguo 山东
3. 普通模式
现在简单的使用 merge into 来实现当符合 on 中的条件时则进行更新操作,否则进行插入操作的功能。查看 target 和 source 表,其中 target 表中的 id 和 source 和 aid 是关联的,现在我们要实现当 target 表的 id 与 source 的 aid 匹配时,使用 source 表的 year 去更新 target 表的 year,否则将 source 中的记录插入到 target 表中,具体实现 sql 如下所示:
merge into target t using source s on (t.id = s.aid)
when matched then
update set t.year = s.year
when not matched then
insert values(s.aid, s.name, s.year);
执行结果如下所示:
SQL> select * from target;
ID NAME YEAR
---------- ------------ ----------
1 liuwei 20 -- 原有记录
2 zhangbin 30 -- 更新为 30
3 fuguo -- 更新为 空
4 yihe 33 -- 新插入记录
4. 只 update
还原 target 表的数据,现在来实现当 target 表的 id 与 source 的 aid 匹配时,使用 source 表的 year 去更新 target 表的 year 的操作,具体实现 sql 如下所示:
merge into target t using (select aid, name, year from source) s on (t.id = s.aid)
when matched then
update set t.year = s.year;
执行结果如下所示:
SQL> select * from target;
ID NAME YEAR
---------- ------------ ----------
1 liuwei 20 -- 原有记录
2 zhangbin 30 -- 更新为 30
3 fuguo -- 更新为 空
还原 target 表的数据,我们也可以在 update 子句添加限定条件,比如使用 source 表的 year 去更新 target 表的 year 的操作时,限定 source 表的记录除了与 target 是匹配的条件之外,city 必须是吉林的,具体实现 sql 如下:
merge into target t using source s on (t.id = s.aid)
when matched then
update set t.year = s.year where s.city = '吉林';
执行结果如下所示:
SQL> select * from target;
ID NAME YEAR
---------- ------------ ----------
1 liuwei 20 -- 原有记录
2 zhangbin 30 -- 更新为 30
3 fuguo 20 -- 原有记录
5. 只 insert
还原 target 表的数据,现在来实现当 target 表的 id 与 source 的 aid 不匹配时,将 source 中的记录插入到 target 表中,具体实现 sql 如下:
merge into target t using source s on (t.id = s.aid)
when not matched then
insert(t.id, t.name, t.year) values(s.aid, s.name, s.year);
执行结果如下所示:
SQL> select * from target;
ID NAME YEAR
---------- ------------ ----------
1 liuwei 20 -- 原有记录
2 zhangbin 21 -- 原有记录
3 fuguo 20 -- 原有记录
4 yihe 33 -- 新增记录
insert 子句也可以添加限定条件,类似 update,这里不再赘述。
6. 无条件 insert
还原 target 表的数据。有时我们需要将一张表中所有的数据插入到另外一张表,此时就可以添加常量过滤谓词来实现,让其只满足匹配和不匹配,这样就只有update或者只有insert。这里我们要无条件全插入,则只需将on中条件设置为永假即可。用 source 表来更新 target 代码如下:
merge into target t using source s on(1 = 0) -- 设置永假匹配条件
when not matched then
insert(t.id, t.name, t.year) values(s.aid, s.name, s.year);
执行结果如下所示:
SQL> select * from target;
ID NAME YEAR
---------- ------------ ----------
1 liuwei 20
2 zhangbin 21
3 fuguo 20
2 zhangbin 30 -- 新增记录
4 yihe 33 -- 新增记录
3 fuguo -- 新增记录
7. 带 delete 的 update
还原 target 表的数据。删除 target 表中与 source 表匹配的符合指定条件的记录。需要注意的是,delete 子句附属于 update 子句,也就是要有 delete 子句必须有 update 子句。如下语句使用 source 表的记录来匹配更新 target 表中的记录。同时删除匹配的记录中 target 表中 id 为2 的记录,具体实现 sql 如下所示:
merge into target t using source s on(t.id = s.aid)
when matched then update set t.year = s.year
delete where(t.id = 2);
执行结果如下所示:
-- id 为 2 的记录被删除
SQL> select * from target;
ID NAME YEAR
---------- ------------ ----------
1 liuwei 20 -- 原有记录
3 fuguo -- year 被更新
今天的文章merge into 的用法分享到此就结束了,感谢您的阅读,如果确实帮到您,您可以动动手指转发给其他人。
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
如需转载请保留出处:https://bianchenghao.cn/24405.html