MERGE INTO

適用於:check marked yes Databricks SQL check marked yes Databricks Runtime

根據源數據表將一組更新、插入和刪除合併至目標 Delta 數據表。

只有 Delta Lake 數據表才支援此語句。

此頁面包含搭配命令使用正確語法 MERGE 的詳細數據。 如需如何使用MERGE作業來管理數據的詳細資訊,請參閱使用合併將Upsert插入 Delta Lake 資料表。

語法

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 | DEFAULT } } [, ...] }

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

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

參數

  • target_table_name

    識別 要修改之數據表的數據表名稱 。 參考的數據表必須是 Delta 數據表。

    數據表不得為外部數據表。

  • target_alias

    目標數據表的數據表別名。 別名不得包含數據行清單。

  • source_table_reference

    數據表名稱,識別要合併至目標數據表的源數據表。

  • source_alias

    數據表的數據表別名 。 別名不得包含數據行清單。

  • ON merge_condition

    一個關聯的數據列如何與另一個關聯的數據列結合。 具有 BOOLEAN 傳回類型的表達式。

  • WHEN MATCHED [ AND matched_condition]

    WHEN MATCHED當源數據列符合以 和 選擇性 match_condition為基礎的merge_condition目標數據表數據列時,會執行 子句。

  • matched_action

    • DELETE

      刪除相符的目標數據表數據列。

      當無條件刪除相符專案時,允許多個相符專案。 無條件刪除並不模棱兩可,即使有多個相符專案也一樣。

    • UPDATE

      更新 相符的目標數據表數據列。

      若要使用來源資料集的對應資料列來更新目標 Delta 資料表的所有資料列,請使用 UPDATE SET *。 這相當於 UPDATE SET col1 = source.col1 [, col2 = source.col2 ...] 目標 Delta 資料表的所有數據行。 因此,此動作假設源數據表的數據行與目標數據表中的數據行相同,否則查詢會擲回分析錯誤。

      注意

      啟用自動架構移轉時,此行為會變更。 如需詳細資訊,請參閱 Delta Lake merge 的自動架構演進。

      適用於:check marked yesDatabricks SQL SQL 倉儲 2022.35 版或更高版本 Databricks Runtime 11.2 和更新版本check marked yes

      您可以指定 DEFAULTexpr ,將資料行明確更新為其預設值。

    如果有多個 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]

    WHEN NOT MATCHED子句會在來源數據列不符合以 和 選擇性 not_matched_condition為基礎的merge_condition任何目標數據列時插入數據列。

    適用於:check marked yes Databricks SQL check marked yes Databricks Runtime 12.1 和更新版本

    WHEN NOT MATCHED BY TARGET 可作為的 WHEN NOT MATCHED別名。

    not_matched_condition 必須是布爾表達式。

    • INSERT *

      使用源數據集的對應數據行,插入目標 Delta 數據表的所有數據行。 這相當於 INSERT (col1 [, col2 ...]) VALUES (source.col1 [, source.col2 ...]) 目標 Delta 資料表的所有數據行。 此動作要求源數據表的數據行與目標數據表中的數據行相同。

      注意

      啟用自動架構移轉時,此行為會變更。 如需詳細資訊,請參閱 Delta Lake merge 的自動架構演進。

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

      新的數據列會根據指定的數據行和對應的表達式產生。 不需要指定目標數據表中的所有數據行。 若為未指定的目標資料列,則會插入資料行預設值,如果 NULL 不存在,則為 。

      適用於:check marked yesDatabricks SQL SQL 倉儲 2022.35 版或更高版本 Databricks Runtime 11.2 和更新版本check marked yes

      您可以將 指定 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]

    適用於:check marked yes Databricks SQL check marked yes Databricks Runtime 12.1 和更新版本

    WHEN NOT MATCHED BY SOURCE 當目標數據列不符合源數據表中任何數據列時,會根據 merge_condition 和 選擇性 not_match_by_source_condition 評估為 true 時執行 子句。

    not_matched_by_source_condition 必須是只參考目標數據表之數據行的布爾表達式。

  • not_matched_by_source_action

    • DELETE

      刪除目標數據表數據列。

    • UPDATE

      更新 目標數據表數據列。 expr 只能參考目標數據表中的數據行,否則查詢會擲回分析錯誤。

      適用於:check marked yesDatabricks SQL SQL 倉儲 2022.35 版或更高版本 Databricks Runtime 11.2 和更新版本check marked yes

      您可以指定 DEFAULTexpr ,將資料行明確更新為其預設值。

    重要

    WHEN NOT MATCHED BY SOURCE當 評估為 false 時merge_condition,新增 子句來更新或刪除目標數據列,可能會導致大量目標數據列遭到修改。 為了獲得最佳效能,請套用 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 錯誤。

    WHEN NOT MATCHED BY SOURCE如果沒有任何條件評估為 true,而目標數據列不符合源數據表中任何數據列,merge_condition則目標數據列會保持不變。

重要

如果源數據集的多個數據列符合並嘗試更新目標 Delta 數據表的相同數據列,作業 MERGE 可能會失敗 ,併發生DELTA_MULTIPLE_SOURCE_ROW_MATCHING_TARGET_ROW_IN_MERGE 錯誤。 根據合併的 SQL 語意,這類更新作業模棱兩可,因為不清楚應該使用哪個來源數據列來更新相符的目標數據列。 您可以預先處理源數據表,以排除多個相符專案的可能性。 請參閱異動數據擷取範例—它會前置處理變更數據集(也就是來源數據集),以在將變更套用至目標 Delta 數據表之前,只保留每個索引鍵的最新變更。

範例

您可以 MERGE INTO 用於重複資料刪除、更新插入變更資料、套用 SCD 類型 2 作業等複雜作業。如需一些範例,請參閱 使用合併 將 Upsert 插入 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