oracle中merge into用法解析

布满荆棘的人生 2022-03-26 09:22 157阅读 0赞

# oracle中merge into用法解析 #

2017年04月11日 10:59:27 [玩人][Link 1] 阅读数：80386

merge into的形式：

1.  `MERGE INTO [target-table] A USING [source-table sql] B ON([conditional expression] and [...]...)`
2.  `WHEN MATCHED THEN`
3.  `[UPDATE sql]`
4.  `WHEN NOT MATCHED THEN`
5.  `[INSERT sql]`

作用:判断Ｂ表和Ａ表是否满足ON中条件，如果满足则用B表去更新A表，如果不满足，则将B表数据插入A表但是有很多可选项，如下:  
1.正常模式

2.只update或者只insert

3.带条件的update或带条件的insert

4.全插入insert实现

5.带delete的update(觉得可以用3来实现)  
下面一一测试。

测试建以下表:

1.  `create table A_MERGE`
2.  `(`
3.  `id NUMBER not null,`
4.  `name VARCHAR2(12) not null,`
5.  `year NUMBER`
6.  `);`
7.  `create table B_MERGE`
8.  `(`
9.  `id NUMBER not null,`
10. `aid NUMBER not null,`
11. `name VARCHAR2(12) not null,`
12. `year NUMBER,`
13. `city VARCHAR2(12)`
14. `);`
15. `create table C_MERGE`
16. `(`
17. `id NUMBER not null,`
18. `name VARCHAR2(12) not null,`
19. `city VARCHAR2(12) not null`
20. `);`
21. `commit;`

其表结构截图如下图所示：

A\_MERGE表结构：

![Center][]

B\_MERGE表结构

![Center 1][]

C\_MERGE表结构

![Center 2][]

**1.正常模式**

先向A\_MERGE和B\_MERGE插入测试数据：

1.  `insert into A_MERGE values(1,'liuwei',20);`
2.  `insert into A_MERGE values(2,'zhangbin',21);`
3.  `insert into A_MERGE values(3,'fuguo',20);`
4.  `commit;`
5.   
6.  `insert into B_MERGE values(1,2,'zhangbin',30,'吉林');`
7.  `insert into B_MERGE values(2,4,'yihe',33,'黑龙江');`
8.  `insert into B_MERGE values(3,3,'fuguo',,'山东');`
9.  `commit;`

此时A\_MERGE和B\_MERGE表中数据截图如下:

A\_MERGE表数据:

![Center 3][]

B\_MERGE表数据:

![Center 4][]

然后再使用merge into用B\_MERGE来更新A\_MERGE中的数据:

1.  `MERGE INTO A_MERGE A USING (select B.AID,B.NAME,B.YEAR from B_MERGE B) C ON (A.id=C.AID)`
2.  `WHEN MATCHED THEN`
3.  `UPDATE SET A.YEAR=C.YEAR`
4.  `WHEN NOT MATCHED THEN`
5.  `INSERT(A.ID,A.NAME,A.YEAR) VALUES(C.AID,C.NAME,C.YEAR);`
6.  `commit;`

此时A\_MERGE中的表数据截图如下:

![Center 5][]  
**2.只update模式**  
首先向B\_MERGE中插入两个数据，来为了体现出只update没有insert，必须有一个数据是A中已经存在的

另一个数据时A中不存在的，插入数据语句如下:

1.  `insert into B_MERGE values(4,1,'liuwei',80,'江西');`
2.  `insert into B_MERGE values(5,5,'tiantian',23,'河南');`
3.  `commit;`

此时A\_MERGE和B\_MERGE表数据截图如下:  
A\_MERGE表数据截图:

![Center 6][]

B\_MERGE表数据截图:

![Center 7][]

然后再次用B\_MERGE来更新A\_MERGE，但是仅仅update，没有写insert部分。

1.  `merge into A_MERGE A USING (select B.AID,B.NAME,B.YEAR from B_MERGE B) C ON(A.ID=C.AID)`
2.  `WHEN MATCHED THEN`
3.  `UPDATE SET A.YEAR=C.YEAR;`
4.   
5.  `commit;`

merge完之后A\_MERGE表数据截图如下:可以发现仅仅更新了AID=1的年龄，没有插入AID=4的数据

![Center 8][]  
**3.只insert模式**  
首先改变B\_MERGE中的一个数据，因为上次测试update时新增的数据没有插入到A\_MERGE，这次可以用。

1.  `update B_MERGE set year=70 where AID=2;`
2.  `commit;`

此时A\_MERGE和B\_MERGE的表数据截图如下:  
A\_MERGE表数据:

![Center 9][]

B\_MERGE表数据:

![Center 10][]

然后用B\_MERGE来更新A\_MERGE中的数据，此时只写了insert，没有写update:

1.  `merge into A_MERGE A USING (select B.AID,B.NAME,B.YEAR from B_MERGE B) C ON(A.ID=C.AID)`
2.  `WHEN NOT MATCHED THEN`
3.  `insert(A.ID,A.NAME,A.YEAR) VALUES(C.AID,C.NAME,C.YEAR);`
4.  `commit;`

此时A\_MERGE的表数据截图如下:

![Center 11][]

**4.带where条件的insert和update。**  
我们在on中进行完条件匹配之后，还可以在后面的insert和update中对on筛选出来的记录再做一次条件判断，用来控制哪些要更新，哪些要插入。  
测试数据的sql代码如下，我们在B\_MERGE修改了两个人名，并且增加了两个人员信息，但是他们来自的省份不同，  
所以我们可以通过添加省份条件来控制哪些能修改，哪些能插入:

1.  `update B_MERGE set name='yihe++' where id=2;`
2.  `update B_MERGE set name='liuwei++' where id=4;`
3.  `insert into B_MERGE values(6,6,'ningqin',23,'江西');`
4.  `insert into B_MERGE values(7,7,'bing',24,'吉安');`
5.  `commit;`

A\_MGERGE表数据截图如下：

![Center 12][]

B\_MERGE表数据：

![Center 13][]

然后再用B\_MERGE去更新A\_MERGE，但是分别在insert和update后面添加了条件限制，控制数据的更新和插入:

1.  `merge into A_MERGE A USING (select B.AID,B.name,B.year,B.city from B_MERGE B) C`
2.  `ON(A.id=C.AID)`
3.  `when matched then`
4.  `update SET A.name=C.name where C.city != '江西'`
5.  `when not matched then`
6.  `insert(A.ID,A.name,A.year) values(c.AID,C.name,C.year) where C.city='江西';`
7.  `commit;`

此时A\_MERGE截图如下:

![Center 14][]

**5.无条件的insert。**  
有时我们需要将一张表中所有的数据插入到另外一张表，此时就可以添加常量过滤谓词来实现，让其只满足  
匹配和不匹配，这样就只有update或者只有insert。这里我们要无条件全插入，则只需将on中条件设置为永假  
即可。用B\_MERGE来更新C\_MERGE代码如下:

1.  `merge into C_MERGE C USING (select B.AID,B.NAME,B.City from B_MERGE B) C ON (1=0)`
2.  `when not matched then`
3.  `insert(C.ID,C.NAME,C.City) values(B.AID,B.NAME,B.City);`
4.  `commit;`

C\_MERGE表在merge之前的数据截图如下:

![Center 15][]

B\_MERGE数据截图如下：

![Center 16][]

C\_MERGE表在merge之后数据截图如下:

![Center 17][]

**6.带delete的update**  
   MERGE提供了在执行数据操作时清除行的选项. 你能够在WHEN MATCHED THEN UPDATE子句中包含DELETE子句.   
DELETE子句必须有一个WHERE条件来删除匹配某些条件的行.匹配DELETE WHERE条件但不匹配ON条件的行不会被从表中删除.  
但我觉得这个带where条件的update差不多，都是控制update，完全可以用带where条件的update来实现。

[Link 1]: https://me.csdn.net/jeryjeryjery
[Center]: /images/20220326/7e3fa715e1564c2b81aaaa39c39b75c3.png
[Center 1]: /images/20220326/82bb47fcb4864de9a25801cded13d382.png
[Center 2]: /images/20220326/51af44f3fadd4ca7858db3fa7b7e1b76.png
[Center 3]: /images/20220326/41d2de7458da48869ae3dd61f3ec30dc.png
[Center 4]: /images/20220326/065ab4a2ea55489fa7262a88a4a58e70.png
[Center 5]: /images/20220326/1fa3a38d72794801ac1540d4fc60d750.png
[Center 6]: /images/20220326/09f081906d6c4882b372f4e72a32f0eb.png
[Center 7]: /images/20220326/62b6db7ee92447cfaf04ec5afdb77257.png
[Center 8]: /images/20220326/bc9be87e04554bed910bbf69ced7b866.png
[Center 9]: /images/20220326/fe66d51b1b4c4fcdaade6ba6f5293a6f.png
[Center 10]: /images/20220326/31b3a484abae455ca24ced492bea2dfe.png
[Center 11]: /images/20220326/325f3bd386b54b5cb33f41911256195e.png
[Center 12]: /images/20220326/f79be7f6c2af4349b9cf5afd56bd8a8e.png
[Center 13]: /images/20220326/1794ebad3ebb48658e4ad33d934682fd.png
[Center 14]: /images/20220326/41318183dc7d4c7981dcb0270e2fc372.png
[Center 15]: /images/20220326/a6dad5d08f6e47579d40f13da75a0fb6.png
[Center 16]: /images/20220326/aa03673a8ede40689dbcc5e1f459fd47.png
[Center 17]: /images/20220326/9c8a0d9feca5452cbd533995b48b261c.png