MERGE INTO

描述

基于源表将一组更新、插入和删除操作合并到目标表中。

语法

MERGE INTO target_table_name [target_alias]
   USING source_table_reference [source_alias]
   ON merge_condition
   { WHEN MATCHED [ AND matched_condition ] THEN matched_action |
     WHEN NOT MATCHED [BY TARGET] [ AND not_matched_condition ] THEN not_matched_action |
     WHEN NOT MATCHED BY SOURCE [ AND not_matched_by_source_condition ] THEN not_matched_by_source_action } [...]

matched_action
 { DELETE |
   UPDATE SET * |
   UPDATE SET { column = { expr } } [, ...] }

not_matched_action
 { INSERT * |
   INSERT (column1 [, ...] ) VALUES ( [expr] [, ...] )

not_matched_by_source_action
 { DELETE |
   UPDATE SET { column = { expr } } [, ...] }

参数

target_table_name
要进行数据修改的表
语法: [ database_name. ] table_name
target_alias
目标表的别名，该别名不能包含表中列的列名
source_table_reference
要合并到目标表的源表
语法: [ database_name. ] table_name
source_alias
源表的别名，该别名不能包含表中列的列名
ON merge_condition
如何将一个关系中的行与另一个关系中的行进行合并。返回类型为布尔类型的表达式。
WHEN MATCHED [ AND matched_condition]
当源行根据 merge_condition 和可选的 match_condition 与目标表行匹配时，将执行 WHEN MATCHED 子句。
matched_action
- DELETE
  删除匹配的目标表行。
  无条件删除匹配项时，允许多个匹配项。即使有多个匹配项，无条件删除也是明确的。
- UPDATE
  更新匹配的目标表行。
  若要使用源表的相应列更新目标表的所有列，请使用 UPDATE SET *。这等效于目标表的所有列 UPDATE SET col1 = source.col1 [, col2 = source.col2 ...]。因此，此操作假定源表的列与目标表的列相同，否则查询将引发分析错误。
如果存在多个 WHEN MATCHED 子句，则会按照它们的指定顺序对其进行求值。每个 WHEN MATCHED 子句（最后一个除外）都必须具有一个 matched_condition。否则，查询将返回 NON_LAST_MATCHED_CLAUSE_OMIT_CONDITION 错误。
如果对于匹配 merge_condition 的源行和目标行对，没有任何 WHEN MATCHED 条件的计算结果为 true，则目标行保持不变。
WHEN NOT MATCHED [BY TARGET] [ AND not_matched_condition]
如果源行根据 merge_condition 和可选的 not_matched_condition 与任何目标行都不匹配，WHEN NOT MATCHED 子句将插入一行。
WHEN NOT MATCHED BY TARGET 可用作 WHEN NOT MATCHED 的别名。
- INSERT *
  使用源数据集的相应列插入目标表的所有列。这等效于目标 Delta 表的所有列 INSERT (col1 [, col2 ...]) VALUES (source.col1 [, source.col2 ...])。此操作要求源表与目标表中的列相同。
  无条件删除匹配项时，允许多个匹配项。即使有多个匹配项，无条件删除也是明确的。
- INSERT ( ... ) VALUES ( ... )
  新行是基于指定的列和相应的表达式生成的。你无需指定目标表中的所有列。对于未指定的目标列，将插入列默认值，如果不存在，则为 NULL。
如果存在多个 WHEN NOT MATCHED 子句，则会按照它们的指定顺序对其进行求值。所有 WHEN NOT MATCHED 子句（最后一个除外）都必须具有多个 not_matched_condition。否则，查询将返回 NON_LAST_NOT_MATCHED_CLAUSE_OMIT_CONDITION 错误。
WHEN NOT MATCHED [BY SOURCE] [ AND not_matched_by_source_condition]
当目标行与源表中的任何行都不匹配（根据 merge_condition 进行匹配）并且可选的 not_match_by_source_condition 计算为 true 时，将执行 WHEN NOT MATCHED BY SOURCE 子句。
not_matched_by_source_condition 必须是仅引用目标表中的列的布尔表达式。
not_matched_by_source_action
- DELETE
  删除目标表行
- UPDATE
  更新目标表行。 expr 只能引用目标表中的列，否则查询将引发分析错误。
添加 WHEN NOT MATCHED BY SOURCE 子句以便在 merge_condition 计算为 false 时更新或删除目标行可能会导致修改大量的目标行。为获得最佳性能，请应用 not_matched_by_source_condition 来限制更新或删除的目标行数。
如果有多个 WHEN NOT MATCHED BY SOURCE 子句，将按照其指定顺序对其进行计算。每个 WHEN NOT MATCHED BY SOURCE 子句（最后一个除外）都必须具有一个 not_matched_by_source_condition。否则，查询将返回 NON_LAST_NOT_MATCHED_BY_SOURCE_CLAUSE_OMIT_CONDITION 错误。
对于不与源表中的任何行匹配的目标行（根据 merge_condition 进行匹配），如果没有任何 WHEN NOT MATCHED BY SOURCE 条件计算为 true，则目标行保持不变。

示例

示例 1


SELECT * FROM t1;
+---------+----------+
|       c1|        c2|
+---------+----------+
|        1|         1|
+---------+----------+
|        2|         2|
+---------+----------+

SELECT * FROM t2;
+---------+----------+
|       c1|        c2|
+---------+----------+
|        2|         5|
+---------+----------+
|        3|         3|
+---------+----------+

MERGE INTO t1 USING t2 ON t1.c1 = t2.c1
WHEN MATCHED THEN
    UPDATE SET *
WHEN NOT MATCHED THEN
    INSERT *

SELECT * FROM t1 ORDER BY c1 ASC;
+---------+----------+
|       c1|        c2|
+---------+----------+
|        1|         1|
+---------+----------+
|        2|         5|
+---------+----------+
|        3|         3|
+---------+----------+

示例 2


SELECT * FROM source;

+---------+-----------+----------+
|     key1|      value|    others|
+---------+-----------+----------+
|        1|          6|       'a'|
+---------+-----------+----------+
|        0|          3|       'b'|
+---------+-----------+----------+

SELECT * FROM target;

+---------+-----------+
|     key2|      value|
+---------+-----------+
|        2|          2|
+---------+-----------+
|        1|          4|
+---------+-----------+

MERGE INTO target as targ USING (SELECT key1, value, others FROM source) src
ON src.key1 = targ.key2
WHEN MATCHED THEN
    UPDATE SET targ.key2 = 20 + key1, value = 20 + src.value
WHEN NOT MATCHED THEN
    INSERT (targ.key2, value) VALUES (key1 - 10, src.value + 10)

SELECT * FROM target ORDER BY key2 ASC;

+---------+-----------+
|     key2|      value|
+---------+-----------+
|      -10|         13|
+---------+-----------+
|        2|          2|
+---------+-----------+
|       21|         26|
+---------+-----------+

示例 3 目前 lake 执行结果不正确


SELECT * FROM source;

+---------+-----------+----------+
|     key1|      value|    others|
+---------+-----------+----------+
|        1|          6|       'a'|
+---------+-----------+----------+
|        0|          3|       'b'|
+---------+-----------+----------+

SELECT * FROM target;

+---------+-----------+
|     key2|      value|
+---------+-----------+
|        2|          2|
+---------+-----------+
|        1|          4|
+---------+-----------+

MERGE INTO target as targ USING (SELECT key1, value, others FROM source) src
ON src.key1 = targ.key2
WHEN MATCHED THEN
    UPDATE SET targ.key2 = 20 + key1, value = 20 + src.value
WHEN NOT MATCHED BY SOURCE THEN
    DELETE

SELECT * FROM target ORDER BY key2 ASC;

+---------+-----------+
|     key2|      value|
+---------+-----------+
|       21|         26|
+---------+-----------+