替换数据

可以使用 MERGE INTO 语句来插入或者更新一条或多条数据。
语法如下：

MERGE INTO [ target_tablename ]
  USING [source_tablename]
  ON ( on_condition )
  [WHEN MATCHED THEN merge_update | merge_delete | DO NOTHING ] 
  [WHEN NOT MATCHED THEN merge_insert | DO NOTHING ]

参数说明：

• target_tablename ：指定需要进行数据替换的表。
• source_tablename ：指定源数据表。
• on_condition ：指定目标表和源表的ON条件，当ON条件为true，走matched分支，否则走not matched分支。
• merge_update ：merge的update部分语句，matched时候执行，如 update set ... where
• merge_delete ：merge的delete部分语句，matched时候执行，必须跟在merge_update之后使用，如delete ... where
• merge_insert ：merge的insert部分语句，not matched时候执行，如 insert values (...)

示例

1、替换表中数据，当源表数据和目标表数据不匹配时候进行insert
当目标表中无数据记录，可以使用 MERGE INTO 语句替换数据，结果等价于通过 INSERT 语句插入数据。
创建 tbase_merge_insert 表后不插入数据，创建源表tbase_source_table并插入一行数据，使用 MERGE INTO 语句往目标表插入一行数据，
merge的on条件为源表第一列 i 等于目标表第一列 i，在不匹配的条件下插入数据。

 tdsql=# create table tbase_merge_insert(i int, j int);
CREATE TABLE
 tdsql=# select * from tbase_merge_insert;
i | j
---+---
(0 rows)
 tdsql=# create table tbase_source_table(i int, j int);
CREATE TABLE
 tdsql=# insert into tbase_source_table values (1,1);
INSERT 0 1
 tdsql=# select * from tbase_source_table;
i | j
---+---
1 | 1
(1 row)

 tdsql=# merge into tbase_merge_insert using tbase_source_table on tbase_merge_insert.i=tbase_source_table.i when not matched then insert values (2,2);
MERGE 1
 tdsql=# select * from tbase_merge_insert;
i | j
---+---
2 | 2
(1 row)

从示例结果可以看出，tbase_merge_insert 表创建后未插入数据，执行 MERGE INTO 语句后，表中插入了一条记录。
merge的on条件改为目标表第一列i不等于源表第一列i，在不匹配的条件下插入数据。

 tdsql=# select * from tbase_merge_insert;
i | j
---+---
2 | 2
(1 row)

 tdsql=# select * from tbase_source_table;
i | j
---+---
1 | 1
(1 row)
 tdsql=# merge into tbase_merge_insert using tbase_source_table on tbase_merge_insert.i!=tbase_source_table.i when not matched then insert values (3,3);
MERGE 0
 tdsql=# select * from tbase_merge_insert;
i | j
---+---
2 | 2
(1 row)

从示例结果可以看出，tbase_merge_insert 表中已有一条记录(2,2)，由于源表中的记录(1,1)满足1 != 2,此时on结果为true，但后续判断是在when not matched插入，所以无法往目标表插入新数据。
2、 替换表中数据，当源表数据和目标表数据匹配时候进行update。
创建目标表tbase_merge_update，插入一条记录(1,1), 此时清空源表，并往源表tbase_source_table也插入一条数据(1,1)，merge语句的on条件改回为第一列i相等的条件。 再使用update去更新tbase_merge_update的记录的j列值为3。

 tdsql=# select * from tbase_source_table;
i | j
---+---
1 | 1
(1 row)

 tdsql=# create table tbase_merge_update (i int, j int);
CREATE TABLE
 tdsql=# insert into tbase_merge_update values (1,1);
INSERT 0 1
 tdsql=# select * from tbase_merge_update;
i | j
---+---
1 | 1
(1 row)
 tdsql=# merge into tbase_merge_update using tbase_source_table on tbase_source_table.i= tbase_merge_update.i when matched then update set j=3 ;
MERGE 1
 tdsql=# select * from tbase_merge_update;
i | j
---+---
1 | 3
(1 row)

从示例结果中可以看出，merge的on条件为true，此时做update目标表将记录(1,1)更新为了(1,3)
update还可以加上where条件做更多的限制，这里我们基于上一步的结果去做merge，merge语句on条件不变，update之后加上where进行更多限制。

 tdsql=# select * from tbase_merge_update;
i | j
---+---
1 | 3
(1 row)

 tdsql=# select * from tbase_source_table;
i | j
---+---
1 | 1
(1 row)
 tdsql=# merge into tbase_merge_update using tbase_source_table on tbase_source_table.i= tbase_merge_update.i when matched then update set j=4 where tbase_merge_update.i > 2 ;
MERGE 0
 tdsql=# select * from tbase_merge_update;
i | j
---+---
1 | 3
(1 row)

结果中可以看出，update之后where限制了当tbase_merge_update表的i列大于2时候才更新，因此merge结果为0行，tbase_merge_update表中没有记录更新。
3、 替换表中数据，当源表数据和目标表数据匹配时候进行delete。
使用 MERGE INTO 语句的 delete功能删除目标表中的记录，创建目标表tbase_merge_delete，插入一条记录(1,1), 此时清空源表，并往源表tbase_source_table也插入一条数据(1,1),merge语句的on条件改回为第一列i相等的条件。

 tdsql=# delete from tbase_source_table;
DELETE 1
 tdsql=# insert into tbase_source_table values(1,1);
INSERT 1
 tdsql=# select * from tbase_source_table;
i | j 
---+---
1 | 1
(1 row)
 tdsql=# create table tbase_merge_delete (i int, j int);
CREATE TABLE
 tdsql=# insert into tbase_merge_delete values (1,1);
INSERT 0 1
 tdsql=# select * from tbase_merge_delete;
i | j 
---+---
1 | 1
(1 row)
 tdsql=# merge into tbase_merge_delete using tbase_source_table on tbase_source_table.i= tbase_merge_delete.i when matched then update set j=10 delete where j=1;
MERGE 1
 tdsql=# select * from tbase_merge_delete;
i | j
---+---
(0 rows)

从示例中可以看出，merge的执行后将目标表tbase_merge_delete中的记录删除了(和Oracle实现有差异，TDSQL PG delete中的j列默认指代源表的j列)
4、 替换表中数据，在merge功能中同时使用insert、update去修改目标表。
创建新的目标表tbase_merge_insup,插入一行记录(2,2,'update'),清空源表数据，再往源表插入(2,2)和(1,1)两条记录。

 tdsql=# create table tbase_merge_insup(i int, j int, op text);
CREATE TABLE
 tdsql=# insert into tbase_merge_insup values (2,2,'update');
INSERT 0 1
 tdsql=# delete from tbase_source_table;
 DELETE 1
 tdsql=# insert into tbase_source_table values(1,1),(2,2);
 INSERT 2
 tdsql=# select * from tbase_source_table;
i | j
---+---
1 | 1
2 | 2
(2 rows)

 tdsql=# select * from tbase_merge_insup;
i | j |   op   
---+---+--------
2 | 2 | update
(1 row)
 tdsql=# merge into tbase_merge_insup using tbase_source_table on tbase_source_table.i = tbase_merge_insup.i when not matched then insert values (1,1,'insert') when matched then update set op='tbase_update';
MERGE 2
 tdsql=# select * from tbase_merge_insup;
i | j |      op      
---+---+--------------
1 | 1 | insert
2 | 2 | tbase_update
(2 rows)

通过以上的例子可以看出，源表的记录(1,1)走到not matched分支，往目标表插入数据(1,1,insert),源表记录(2,2)走到matched分支，更新目标表插入的记录(2,2,update)为(2,2,tbase_update)。
5、 替换表中数据，在merge功能中同时使用insert、delete去修改目标表
创建新的目标表tbase_merge_insdel,插入一行记录(2,2,'delete'),清空源表数据，再往源表插入(2,2)和(1,1)两条记录。

 tdsql=# create table tbase_merge_insdel(i int, j int, op text);
CREATE TABLE
 tdsql=# insert into tbase_merge_insdel values (2,2,'delete');
INSERT 0 1
 tdsql=# select * from tbase_merge_insdel;
i | j |   op   
---+---+--------
2 | 2 | delete
(1 row)
 tdsql=# delete from tbase_source_table;
 DELETE 2
 tdsql=# insert into tbase_source_table values(1,1),(2,2);
 INSERT 2
 tdsql=# select * from tbase_source_table;
i | j
---+---
1 | 1
2 | 2
(2 rows)
 tdsql=# merge into tbase_merge_insdel using tbase_source_table on tbase_source_table.i = tbase_merge_insdel.i when not matched then insert values (1,1,'insert') when matched then update set j=10 delete where j=2;
MERGE 2
 tdsql=# select * from tbase_merge_insdel;
i | j |   op   
---+---+--------
 1 |  1 | insert
(2 rows)

通过以上的例子可以看出，源表的记录(1,1)走到not matched分支，往目标表插入数据(1,1,insert),源表记录(2,2)走到matched分支，删除了目标表插入的记录(2,2,delete)。(和Oracle实现有差异，TDSQL PG delete中的j列默认指代源表的j列)
6、 oracle兼容模式下，使用merge替换表数据，可以同时使用insert、update、delete修改目标表数据
创建新的目标表tbase_merge_all,插入两行记录(2,2,'delete'),(2,2,update)，清空源表数据，再往源表插入(2,2)和(1,1)两条记录。

 tdsql=# create table tbase_merge_all(i int, j int, op text);
CREATE TABLE
 tdsql=# insert into tbase_merge_all values(2,2,'delete');
INSERT 0 1
 tdsql=# insert into tbase_merge_all values(2,2,'update');
INSERT 0 1
 tdsql=# select * from tbase_merge_all;
i | j |   op   
---+---+--------
2 | 2 | delete
2 | 2 | update
(3 rows)
 tdsql=# delete from tbase_source_table;
 DELETE 2
 tdsql=# insert into tbase_source_table values(1,1),(2,2);
 INSERT 2
 tdsql=# select * from tbase_source_table;
i | j
---+---
1 | 1
2 | 2
(2 rows)
 tdsql=# merge into tbase_merge_all using tbase_source_table on tbase_source_table.i = tbase_merge_all.i when not matched then insert values (1,1,'insert') when matched then update set op='tbase_update' where op='update' delete where op='tbase_update' or op='delete';
MERGE 2
 tdsql=# select * from tbase_merge_all;
i | j |   op   
---+---+--------
2 | 2 | delete
1 | 1 | insert
(2 rows)

通过以上的例子可以看出：

• 源表的记录(1,1)走到not matched分支，往目标表插入数据(1,1,insert),
• 源表记录(2,2)走到matched分支，匹配了目标表2行记录(2,2,'update'),(2,2,'delete''),先通过update操作更新了目标表记录(2,2,'update')为(2,2,'tbase_update')，之后delete待操作的集合就变为update操作之后的结果记录(2,2,'tbase_update')而不包括(2,2,'delete')这条记录，因此delete通过where条件只能把这条记录删除，记录(2,2,'delete')不受影响。