MERGE INTO

适用于： Databricks SQL Databricks Runtime

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

只有 Delta Lake 表支持此语句。

本页包含有关使用 MERGE 命令的正确语法的详细信息。 有关如何使用 MERGE 操作来管理数据的更多指导，请参阅使用合并更新插入到 Delta Lake 表。

语法

MERGE [ WITH SCHEMA EVOLUTION ] 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 | DEFAULT } } [, ...] }

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

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

参数

• WITH SCHEMA EVOLUTION

  适用于：Databricks Runtime 15.2 及更高版本

  为此MERGE操作启用自动架构演变功能。 启用后，将自动更新目标 Delta 表的架构，以匹配源表的架构。

• target_table_name

  一个表名称，用于识别要修改的表。 引用的表必须是 Delta 表。

  名称不得包含 选项规范。

  该表不得为外表。

• target_alias

  目标表的表别名。 该别名不得包含列的列表。

• source_table_reference

  一个表名称，用于识别要合并到目标表的源表。

• source_alias

  源表的表别名。 该别名不得包含列的列表。

• ON merge_condition

  如何将一个关系中的行与另一个关系中的行进行合并。 返回类型为布尔类型的表达式。

• WHEN MATCHED [ AND matched_condition]

  当源行根据 merge_condition 和可选的 match_condition 与目标表行匹配时，将执行 WHEN MATCHED 子句。

• matched_action

  • DELETE

    删除匹配的目标表行。

    无条件删除匹配项时，允许多个匹配项。 即使有多个匹配项，无条件删除也是明确的。

  • UPDATE

    更新匹配的目标表行。

    若要使用源数据集的相应列更新目标 Delta 表的所有列，请使用 UPDATE SET *。 这等效于目标 Delta 表的所有列 UPDATE SET col1 = source.col1 [, col2 = source.col2 ...]。 因此，此操作假定源表的列与目标表的列相同，否则查询将引发分析错误。

    注意

    启用自动架构迁移后，此行为将发生变化。 有关详细信息，请参阅 Delta Lake 合并的自动架构演变。

    适用于： Databricks SQL Databricks Runtime 11.3 LTS 及更高版本

    可以指定 DEFAULT 作为 expr 以将列显式更新为其默认值。

  如果存在多个 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 子句将插入一行。

  适用于： Databricks SQL Databricks Runtime 12.2 LTS 及更高版本

  WHEN NOT MATCHED BY TARGET 可用作 WHEN NOT MATCHED 的别名。

  not_matched_condition 必须为布尔表达式。

  • INSERT *

    使用源数据集的相应列插入目标 Delta 表的所有列。 这等效于目标 Delta 表的所有列 INSERT (col1 [, col2 ...]) VALUES (source.col1 [, source.col2 ...])。 此操作要求源表与目标表中的列相同。

    注意

    启用自动架构迁移后，此行为将发生变化。 有关详细信息，请参阅 Delta Lake 合并的自动架构演变。

  • INSERT ( ... ) VALUES ( ... )

    新行是基于指定的列和相应的表达式生成的。 你无需指定目标表中的所有列。 对于未指定的目标列，将插入列默认值，如果不存在，则为 NULL。

    适用于： Databricks SQL Databricks Runtime 11.3 LTS 及更高版本

    可以指定 DEFAULT 作为表达式来显式插入目标列的列默认值。

  如果存在多个 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]

  适用于： Databricks SQL Databricks Runtime 12.2 LTS 及更高版本

  当目标行与源表中的任何行都不匹配（根据 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 只能引用目标表中的列，否则查询将引发分析错误。

    适用于： Databricks SQL Databricks Runtime 11.3 LTS 及更高版本

    可以指定 DEFAULT 作为 expr 以将列显式更新为其默认值。

  重要

  添加 WHEN NOT MATCHED BY SOURCE 子句以便在 merge_condition 计算为 false 时更新或删除目标行可能会导致修改大量的目标行。 为获得最佳性能，请应用 not_matched_by_source_condition 来限制更新或删除的目标行数。

  如果有多个 WHEN NOT MATCHED BY SOURCE clauses，将按照其指定顺序对其进行计算。 每个 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，则目标行保持不变。

重要

如果根据 ON 和 WHEN MATCHED 子句中指定的条件，源表中的多个行与目标表中的同一行匹配，则 MERGE 操作将失败，并出现 DELTA_MULTIPLE_SOURCE_ROW_MATCHING_TARGET_ROW_IN_MERGE 错误。 根据合并的 SQL 语义，这种更新操作的类型模棱两可，因为尚不清楚应使用哪个源行来更新匹配的目标行。 你可以预处理源表来消除出现多个匹配项的可能性。 请参阅变更数据捕获示例。 此示例对变更数据集（即源数据集）进行预处理，来仅保留每键的最新更改，然后再将更改应用到目标 Delta 表中。 在 Databricks Runtime 15.4 LTS 及更低版本中，在评估多个匹配项之前，MERGE 只考虑 ON 子句中的条件。

示例

可以将 MERGE INTO 用于复杂的操作，如删除重复数据、更新插入更改数据、应用 SCD 类型 2 操作等。请参阅使用合并以更新插入的方式插入到 Delta Lake 表中获取一些示例。

WHEN MATCHED

-- Delete all target rows that have a match in the source table.
> MERGE INTO target USING source
  ON target.key = source.key
  WHEN MATCHED THEN DELETE

-- Conditionally update target rows that have a match in the source table using the source value.
> MERGE INTO target USING source
  ON target.key = source.key
  WHEN MATCHED AND target.updated_at < source.updated_at THEN UPDATE SET *

-- Multiple MATCHED clauses conditionally deleting matched target rows and updating two columns for all other matched rows.
> MERGE INTO target USING source
  ON target.key = source.key
  WHEN MATCHED AND target.marked_for_deletion THEN DELETE
  WHEN MATCHED THEN UPDATE SET target.updated_at = source.updated_at, target.value = DEFAULT

WHEN NOT MATCHED [BY TARGET]

-- Insert all rows from the source that are not already in the target table.
> MERGE INTO target USING source
  ON target.key = source.key
  WHEN NOT MATCHED THEN INSERT *

-- Conditionally insert new rows in the target table using unmatched rows from the source table.
> MERGE INTO target USING source
  ON target.key = source.key
  WHEN NOT MATCHED BY TARGET AND source.created_at > now() - INTERVAL “1” DAY THEN INSERT (created_at, value) VALUES (source.created_at, DEFAULT)

WHEN NOT MATCHED BY SOURCE

-- Delete all target rows that have no matches in the source table.
> MERGE INTO target USING source
  ON target.key = source.key
  WHEN NOT MATCHED BY SOURCE THEN DELETE

-- Multiple NOT MATCHED BY SOURCE clauses conditionally deleting unmatched target rows and updating two columns for all other matched rows.
> MERGE INTO target USING source
  ON target.key = source.key
  WHEN NOT MATCHED BY SOURCE AND target.marked_for_deletion THEN DELETE
  WHEN NOT MATCHED BY SOURCE THEN UPDATE SET target.value = DEFAULT

WITH SCHEMA EVOLUTION

-- Multiple MATCHED and NOT MATCHED clauses with schema evolution enabled.
> MERGE WITH SCHEMA EVOLUTION INTO target USING source
  ON source.key = target.key
  WHEN MATCHED THEN UPDATE SET *
  WHEN NOT MATCHED THEN INSERT *
  WHEN NOT MATCHED BY SOURCE THEN DELETE

相关文章

• DELETE
• INSERT INTO
• UPDATE