MogDB/openGauss 中 merge 的語法解析
近期瞭解學習了 MogDB/openGauss 中 merge 的使用，merge 語法是根據源表對目標表進行匹配查詢，匹配成功時更新，不成功時插入。簡單來說就是有則更新，無則插入，語句簡潔，效率高。
下面展示 MogDB/openGauss 中 merge 的語法
openGauss=# \h merge
Command: MERGE
Description: insert, update, or delete rows of a table based upon source data
Syntax:
MERGE [/*+ plan_hint */] INTO table_name [ [ AS ] alias ]
USING { { table_name | view_name } | subquery } [ [ AS ] alias ]
ON ( condition )
[
WHEN MATCHED THEN
UPDATE SET { column_name = { expression | DEFAULT } |
( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
[ WHERE condition ]
]
[
WHEN NOT MATCHED THEN
INSERT { DEFAULT VALUES |
[ ( column_name [, ...] ) ] VALUES ( { expression | DEFAULT } [, ...] ) [, ...] [ WHERE condition ] }
];
建立測試表
merge 有幾種匹配條件可以交叉選擇。 作用: 判斷源表和目標表是否滿足合併的條件 如果滿足
用源表去更新目標表
用源表去刪除目標表
什麼也不幹
如果不滿足
用源表去插入目標表
什麼也不幹
建立出滿足的表
create table a_merge (
id int not null,
name varchar not null,
year int
);
create table b_merge (
id int not null,
aid int not null,
name varchar not null,
year int,
city varchar
);
create table c_merge (
id int not null,
name varchar not null,
city varchar not null
);
測試一：匹配則修改，無則插入
--插入資料
insert into a_merge values(1,'liuwei',20);
insert into a_merge values(2,'zhangbin',21);
insert into a_merge values(3,'fuguo',20);
insert into b_merge values(1,2,'zhangbin',30,'吉林');
insert into b_merge values(2,4,'yihe',33,'黑龍江');
insert into b_merge (id,aid,name,city) values(3,3,'fuguo','山東');
--資料對比
select * from a_merge; select * from b_merge;
id | name | year
----+----------+------
1 | liuwei | 20
2 | zhangbin | 21
3 | fuguo | 20
(3 rows)
id | aid | name | year | city
----+-----+----------+------+--------
1 | 2 | zhangbin | 30 | 吉林
2 | 4 | yihe | 33 | 黑龍江
3 | 3 | fuguo | | 山東
(3 rows)
--merge語句
merge into a_merge a
using (select b.aid,b.name,b.year from b_merge b) c on (a.id=c.aid)
when matched then
update set year=c.year
when not matched then
insert values(c.aid,c.name,c.year);
--更新後的a_merge表
select * from a_merge;
id | name | year
----+----------+------
1 | liuwei | 20
2 | zhangbin | 30
3 | fuguo |
4 | yihe | 33
(4 rows)
測試二：匹配則修改，無則不操作
--插入資料
insert into b_merge values(4,1,'liuwei',80,'江西');
insert into b_merge values(5,5,'tiantian',23,'河南');
--核對資料
select * from a_merge;select * from b_merge;
id | name | year
----+----------+------
1 | liuwei | 20
2 | zhangbin | 30
3 | fuguo |
4 | yihe | 33
(4 rows)
id | aid | name | year | city
----+-----+----------+------+--------
1 | 2 | zhangbin | 30 | 吉林
2 | 4 | yihe | 33 | 黑龍江
3 | 3 | fuguo | | 山東
4 | 1 | liuwei | 80 | 江西
5 | 5 | tiantian | 23 | 河南
(5 rows)
--merge語句
merge into a_merge a
using (select b.aid,b.name,b.year from b_merge b) c on (a.id=c.aid)
when matched then
update set year=c.year;
--資料對比
select * from a_merge;
id | name | year
----+----------+------
1 | liuwei | 80
2 | zhangbin | 30
3 | fuguo |
4 | yihe | 33
(4 rows)
測試三：匹配無操作，不匹配進行 insert
--修改測試資料
update b_merge set year=70 where aid=2;
--兩表對比
select * from a_merge;select * from b_merge;
id | name | year
----+----------+------
1 | liuwei | 80
2 | zhangbin | 30
3 | fuguo |
4 | yihe | 33
(4 rows)
id | aid | name | year | city
----+-----+----------+------+--------
2 | 4 | yihe | 33 | 黑龍江
3 | 3 | fuguo | | 山東
4 | 1 | liuwei | 80 | 江西
5 | 5 | tiantian | 23 | 河南
1 | 2 | zhangbin | 70 | 吉林
(5 rows)
--merge語句
merge into a_merge a
using (select b.aid,b.name,b.year from b_merge b) c on (a.id=c.aid)
when not matched then
insert values(c.aid,c.name,c.year);
--檢視a_merge表
select * from a_merge;
id | name | year
----+----------+------
1 | liuwei | 80
2 | zhangbin | 30
3 | fuguo |
4 | yihe | 33
5 | tiantian | 23
(5 rows)
測試四：一律 insert
--merge語句
merge into c_merge c
using (select b.aid,b.name,b.city from b_merge b) b on (1=0)
when not matched then
insert values(b.aid,b.name,b.city);
--檢視兩表，條數相同
select * from c_merge ;select * from b_merge ;
id | name | city
----+----------+--------
3 | fuguo | 山東
5 | tiantian | 河南
2 | zhangbin | 吉林
4 | yihe++ | 黑龍江
1 | liuwei++ | 江西
6 | ningqin | 江西
7 | bing | 吉安
(7 rows)
id | aid | name | year | city
----+-----+----------+------+--------
3 | 3 | fuguo | | 山東
5 | 5 | tiantian | 23 | 河南
1 | 2 | zhangbin | 70 | 吉林
2 | 4 | yihe++ | 33 | 黑龍江
4 | 1 | liuwei++ | 80 | 江西
6 | 6 | ningqin | 23 | 江西
7 | 7 | bing | 24 | 吉安
(7 rows)