#MERGE

# 通用描述

MERGE语句整合了多步的SELECT/UPDATE/INSERT/DELETE操作,使用一次该语句即可实现根据条件(Condition)把源(Source)对象记录整合到目标(Target)对象的功能。

本语句适用于单机HEAP/TAC表和共享集群部署。

语句中的merge_update_clause和merge_insert_clause至少要定义一个。

MERGE语句是对SELECT/UPDATE/INSERT/DELETE的操作整合,也受到在其语句中出现的SELECT/UPDATE/INSERT/DELETE语句相同的条件约束。

# 语句定义

merge::=

syntax
MERGE hint INTO target_table_clause USING source_table_clause ON ( condition ) merge_update_clause merge_insert_clause

target_table_clause::=

syntax
schema . table partition_extension_clause t_alias

partition_extension_clause::=

syntax
partition ( partition ) for ( partition_key_value , )

source_table_clause::=

syntax
schema . table partition_extension_clause view ( subquery ) t_alias

subquery查看SELECT中描述

merge_update_clause::=

syntax
WHEN MATCHED THEN UPDATE SET column = expr DEFAULT , where_clause DELETE where_clause

merge_insert_clause::=

syntax
WHEN NOT MATCHED THEN INSERT ( column , ) VALUES ( expr DEFAULT , ) where_clause

# hint

该语句用于提出给定的方案到优化器(Optimizer ),使其按照此方案生成语句的执行计划。查看hint说明

# target_table_clause

该语句用于指定MERGE操作的目标,即表或表分区。

# table

目标表的名称。

# partition_extension_clause

INSERT语句中的描述一致。

# t_alias

定义别名。

# source_table_clause

该语句用于指定MERGE操作的源,可以为表|表分区、视图或子查询的结果集。

# view

视图的名称。

# subquery

SELECT语句中的描述一致。

# condition

该语句为YashanDB通用条件语句,用于指定MERGE选择下一步操作的条件,对目标表里的每行记录执行此条件判断,为真(MATCHED)时,将执行merge_update_clause;为假(NOT MATCHED)时,将执行merge_insert_clause。

condition可以为任何能产生Boolean结果的表达式,例如:

  • TRUE,Boolean结果为TRUE。
  • 1=2,Boolean结果为FALSE。
  • target.column=source.column,当Target某行按column字段在Source中能匹配到行记录时,Boolean结果为TRUE,否则为FALSE。

# merge_update_clause

该语句用于在condition结果为真时,对目标表指定的列字段执行UPDATE语句,指定多列用,分隔。 存在UPDATE子句时,会让MERGE语句中触发的INSERT、DELETE触发器中UPDATING ('*column*')条件谓词的结果在更新的列匹配时返回TRUE。

对列字段赋值的数据可以为:

  • expr表达式
  • DEFAULT:与UPDATE语句中的DEFAULT使用方法一致。

# where_clause

对于目标表中根据上面ON condition进入到UPDATE语句的行记录,执行一次条件判断(语法同condition),满足条件的行记录才执行数据的更新。

# delete where_clause

对于目标表中已被执行UPDATE语句的行记录,执行一次条件判断(语法同condition),满足条件的记录将从目标表中删除。 DELETE时会直接执行DELETE操作而不是先执行UPDATE再DELETE,该行为会影响UPDATE/DELETE事件触发器的表现。

示例(单机HEAP/TAC表、共享集群部署)

-- employees为一张员工信息表,包含如下五条数据
SELECT branch,department,employee_no,employee_name,sex,entry_date FROM employees;
BRANCH DEPARTMENT EMPLOYEE_NO   EMPLOYEE_NAME SEX   ENTRY_DATE                       
------ ---------- ------------- ------------- ----- -------------------------------- 
0101   000        0101000001    Mask          1     2020-09-08                   
0101   000        0101000002    John          1     2017-12-13                  
0201   010        0201010011    Anna          0     2022-08-09                  
0201   008        0201008003    Jack          1     2021-07-05                  
0101   008        0201008004    Jim           1     2022-11-17 
  
-- 创建与employees同构的employees_merge表
CREATE TABLE employees_merge AS SELECT * FROM employees WHERE 1=2;
INSERT INTO employees_merge VALUES ('0101','008','0201008003','Jim','0',DATE '2021-11-17');
INSERT INTO employees_merge VALUES ('0101','000','0101000002','John','1',DATE '2021-11-17');
COMMIT;
  
-- 将eemployees_merge表中的数据merge
  
-- 由于0201008003号员工在employees中存在两行记录,需利用此处的department与branch组合条件过滤成一条,否则更新失败
MERGE INTO employees_merge b
USING (SELECT * FROM employees) a
ON (a.employee_no=b.employee_no)
WHEN MATCHED THEN UPDATE SET b.sex=a.sex,b.entry_date=a.entry_date
WHERE a.department='008' AND a.branch='0201';
  
-- employees_merge表merge后数据被更新如下
SELECT branch,department,employee_no,employee_name,sex,entry_date FROM employees_merge;
BRANCH DEPARTMENT EMPLOYEE_NO   EMPLOYEE_NAME SEX   ENTRY_DATE                       
------ ---------- ------------- ------------- ----- -------------------------------- 
0101   008        0201008003    Jim           1     2021-07-05                  
0101   000        0101000002    John          1     2021-11-17
  
-- 添加DELETE语句后重新merge,employees_merge表的Jim员工数据先被更新然后被删除
MERGE INTO employees_merge b
USING (SELECT * FROM employees) a
ON (a.employee_no=b.employee_no)
WHEN MATCHED THEN UPDATE SET b.sex=a.sex,b.entry_date=a.entry_date
WHERE a.department='008' AND a.branch='0201'
DELETE WHERE b.department='008';
  
SELECT branch,department,employee_no,employee_name,sex,entry_date FROM employees_merge;
BRANCH DEPARTMENT EMPLOYEE_NO   EMPLOYEE_NAME SEX   ENTRY_DATE                       
------ ---------- ------------- ------------- ----- -------------------------------- 
0101   000        0101000002    John          1     2021-11-17

# merge_insert_clause

该语句用于在condition结果为假时,对目标表执行INSERT语句。不指定列字段表示按目标表定义的列字段顺序逐个匹配,指定多个列字段用,分隔。

对列字段赋值的数据可以为:

  • expr表达式
  • DEFAULT:与INSERT语句中的DEFAULT使用方法一致。

# where_clause

对于目标表中根据上面ON condition进入到INSERT语句的行记录,执行一次条件判断(语法同condition),满足条件的行记录才插入到目标表中。

示例(单机HEAP/TAC表、共享集群部署)

-- employees为一张员工信息表,包含如下五条数据
SELECT branch,department,employee_no,employee_name,sex,entry_date FROM employees;
BRANCH DEPARTMENT EMPLOYEE_NO   EMPLOYEE_NAME SEX   ENTRY_DATE                       
------ ---------- ------------- ------------- ----- -------------------------------- 
0101   000        0101000001    Mask          1     2020-09-08                   
0101   000        0101000002    John          1     2017-12-13                   
0201   010        0201010011    Anna          0     2022-08-09                   
0201   008        0201008003    Jack          1     2021-07-05                  
0101   008        0201008004    Jim           1     2022-11-17
  
-- 创建与employees同构的employees3表
CREATE TABLE employees3 AS SELECT * FROM employees WHERE 1=2;
INSERT INTO employees3 VALUES ('0101','000','0101000002','John','1',DATE '2021-11-17');
COMMIT;
  
-- 将empoyees1表中的数据merge
MERGE INTO employees3 b
USING (SELECT * FROM employees) a
ON (a.employee_no=b.employee_no)
WHEN MATCHED THEN UPDATE SET b.sex=a.sex,b.entry_date=a.entry_date
WHEN NOT MATCHED THEN INSERT VALUES (a.branch,a.department,a.employee_no,a.employee_name,a.sex,a.entry_date);
  
-- employees3中的John员工记录被更新,且在employees中没有的记录被插入
SELECT branch,department,employee_no,employee_name,sex,entry_date FROM employees3;
BRANCH DEPARTMENT EMPLOYEE_NO   EMPLOYEE_NAME SEX   ENTRY_DATE                       
------ ---------- ------------- ------------- ----- -------------------------------- 
0101   000        0101000002    John          1     2017-12-13                 
0101   000        0101000001    Mask          1     2020-09-08                  
0201   010        0201010011    Anna          0     2022-08-09                   
0201   008        0201008003    Jack          1     2021-07-05                   
0101   008        0201008004    Jim           1     2022-11-17