MERGE INTO
適用於:Databricks SQL
Databricks Runtime
根據源數據表將一組更新、插入和刪除合併至目標 Delta 數據表。
只有 Delta Lake 數據表才支援此語句。
此頁面包含搭配命令使用正確語法 MERGE
的詳細數據。 如需更詳細的資訊以瞭解如何使用
語法
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 和更新版本
啟用此
作業的自動架構演進 。 啟用時,目標 Delta 資料表的架構會自動更新,以符合源數據表的架構。 -
資料表名稱 識別要修改的數據表。 參考的數據表必須是 Delta 數據表。
名稱不得包含 選項規格。
數據表不得為外部數據表。
-
目標資料表的 表別名。 別名不得包含欄位清單。
-
數據表名稱 識別要合併至目標數據表的源數據表。
-
源表的 表別名。 別名不得包含欄位清單。
-
一個關聯的數據列如何與另一個關聯的數據列結合。 具有 BOOLEAN 傳回類型的表達式。
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 merge 的自動架構演進。
適用於:
Databricks SQL
Databricks Runtime 11.3 LTS 和更新版本
您可以將
DEFAULT
指定為expr
,以明確將數據行更新為其預設值。
如果有多個
WHEN MATCHED
子句,則會依照指定的順序來評估它們。 除了最後一個子句之外,每個WHEN MATCHED
子句都必須有matched_condition
。 否則,查詢會傳 回NON_LAST_MATCHED_CLAUSE_OMIT_CONDITION 錯誤。如果符合的來源和目標數據列組
WHEN MATCHED
沒有任何merge_condition
條件評估為 true,則目標數據列會保持不變。WHEN NOT MATCHED [BY TARGET] [ AND
not_matched_condition]
當來源數據列與任何目標數據列不匹配時,
WHEN NOT MATCHED
子句會根據merge_condition
和選擇性not_matched_condition
插入一行。適用於:
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 merge 的自動架構演進。
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 和更新版本
當目標數據行與源數據表中的任何行不匹配,並且可選的
not_match_by_source_condition
評估為 true 時,會根據merge_condition
執行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
,以明確將數據行更新為其預設值。
重要
當
merge_condition
評估為 false 時,新增WHEN NOT MATCHED BY SOURCE
子句來更新或刪除目標數據列,可能會導致大量目標數據列遭到修改。 為了獲得最佳效能,請套用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 Type 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
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