0%

merge into 的用法

发表于 分类于 阅读次数： Valine：
本文字数： 4.8k 阅读时长 ≈ 4 分钟

1. 语法

merge into 的语法如下所示：

1
2
3
4
5
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. 测试表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- 目标表
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','','山东');

两表的的查询结果如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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 如下所示：

1
2
3
4
5
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);

执行结果如下所示：

1
2
3
4
5
6
7
8
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 如下所示：

1
2
3
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;

执行结果如下所示：

1
2
3
4
5
6
7
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 如下：

1
2
3
merge into target t using source s on (t.id = s.aid)
when matched then
update set t.year = s.year where s.city = '吉林';

执行结果如下所示：

1
2
3
4
5
6
7
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 如下：

1
2
3
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);

执行结果如下所示：

1
2
3
4
5
6
7
8
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 代码如下:

1
2
3
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);

执行结果如下所示：

1
2
3
4
5
6
7
8
9
10
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 如下所示：

1
2
3
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);

执行结果如下所示：

1
2
3
4
5
6
7
-- id 为 2 的记录被删除
SQL> select * from target;

        ID NAME               YEAR
---------- ------------ ----------
         1 liuwei               20 -- 原有记录
         3 fuguo                   -- year 被更新
------ 本文结束------