本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。
MERGE
有條件地將來源資料表中的資料列合併到目標資料表中。傳統上,這只能透過單獨使用多個插入、更新或刪除陳述式來實現。如需有關MERGE可讓您合併之作業的詳細資訊 UPDATE,請參閱DELETE、和INSERT。
語法
MERGE INTO target_table USING source_table [ [ AS ] alias ] ON match_condition [ WHEN MATCHED THEN { UPDATE SET col_name = { expr } [,...] | DELETE } WHEN NOT MATCHED THEN INSERT [ ( col_name [,...] ) ] VALUES ( { expr } [, ...] ) | REMOVE DUPLICATES ]
參數
- target_table
-
MERGE陳述式合併到的暫存或永久資料表。
- source_table
-
提供要合併到 target_table 之資料列的暫存或永久資料表。source_table 也可以是 Spectrum 資料表。source_table 不能是檢視或子查詢。
- alias
-
source_table 暫時替代名稱。
此為選用參數。以 AS 開頭的 alias 也是選用項目。
- match_condition
-
指定來源資料表資料欄與目標資料表資料欄之間的相等述詞,這些資料欄的作用是判斷 source_table 中的資料列是否可與 target_table 中的資料列相符。如果符合條件,則MERGE執行該資料列的 matched_子句。否則,為MERGE該行運行沒有匹配的子句。
- WHEN MATCHED
-
指定當來源資料列與目標資料列之間的比對條件評估為 True 時,要執行的動作。您可以指定UPDATE動作或DELETE動作。
- UPDATE
更新 target_table 中的相符資料列。只會更新 col_name 中您指定的值。
- DELETE
刪除 target_table 中的相符資料列。
- WHEN NOT MATCHED
-
指定當比對條件評估為 False 或未知時要執行的動作。您只能指定此子句的INSERT插入動作。
- INSERT
將一個資料列插入 target_table。您可以依任意順序列出目標 col_name。如果您不提供任何 col_name 值,則預設順序是按所有資料表資料欄的宣告順序排序。
- col_name
-
您要修改的一個或多個資料欄名稱。不要在指定目標資料欄時包含資料表名稱。
- expr
-
定義 col_name 新值的運算式。
- REMOVE DUPLICATES
-
指定指MERGE令以簡化模式執行。簡化模式有以下要求:
target_table 和 source_table 必須具有相同數目的資料欄和相容的資料欄類型。
省略命令中的WHEN子INSERT句UPDATE和 MERGE and 子句。
在MERGE命令中使用REMOVEDUPLICATES子句。
在簡化模式中,MERGE執行下列操作:
target_table 中具有 source_table 中相符項目的資料列會更新,以符合 source_table 中的值。
source_table 中沒有 target_table 中相符項目的資料列會插入到 target_table 中。
當 target_table 中的多個資料列與 source_table 中的相同資料列相符時,重複的資料列會移除。Amazon Redshift 會保留一個資料列並對其進行更新。與 source_table 中的資料列不相符的重複資料列保持不變。
使用REMOVEDUPLICATES可提供比使用WHENMATCHED和更好的性能WHENNOTMATCHED。REMOVEDUPLICATES如果目標表和源表是兼容的,並且您不需要在目標表中保留重複的行,我們建議使用。
使用須知
-
若要執行MERGE陳述式,您必須是 source_table 和 target_table 的擁有者,或具有這些資料表的權限。SELECT此外,根據陳述式中包含的作業DELETE,您必須擁有 UPDATE target_table 的、和INSERT權限。MERGE
-
target_table 不能是系統資料表、目錄資料表或外部資料表。
-
source_table 和 target_table 不能是相同資料表。
-
您不能在陳述式中使用WITH子MERGE句。
-
target_table 的資料列無法比對 source_table 中的多個資料列。
請思考下列範例:
CREATE TABLE target (id INT, name CHAR(10)); CREATE TABLE source (id INT, name CHAR(10)); INSERT INTO target VALUES (1, 'Bob'), (2, 'John'); INSERT INTO source VALUES (1, 'Tony'), (1, 'Alice'), (3, 'Bill'); MERGE INTO target USING source ON target.id = source.id WHEN MATCHED THEN UPDATE SET id = source.id, name = source.name WHEN NOT MATCHED THEN INSERT VALUES (source.id, source.name); ERROR: Found multiple matches to update the same tuple. MERGE INTO target USING source ON target.id = source.id WHEN MATCHED THEN DELETE WHEN NOT MATCHED THEN INSERT VALUES (source.id, source.name); ERROR: Found multiple matches to update the same tuple.
在這兩個MERGE陳述式中,作業都會失敗,因為資料
source
表中有多個資料列的 ID 值1
。 -
匹配條件和 expr 不能部分引SUPER用類型列。例如,如果您的SUPER型別物件是陣列或結構,就無法將該資料欄的個別元素用於 match_condition 或 expr,但您可以使用整個資料欄。
請思考下列範例:
CREATE TABLE IF NOT EXISTS target (key INT, value SUPER); CREATE TABLE IF NOT EXISTS source (key INT, value SUPER); INSERT INTO target VALUES (1, JSON_PARSE('{"key": 88}')); INSERT INTO source VALUES (1, ARRAY(1, 'John')), (2, ARRAY(2, 'Bill')); MERGE INTO target USING source ON target.key = source.key WHEN matched THEN UPDATE SET value = source.value[0] WHEN NOT matched THEN INSERT VALUES (source.key, source.value[0]); ERROR: Partial reference of SUPER column is not supported in MERGE statement.
如需有關SUPER類型的詳細資訊,請參閱SUPER類型。
-
如果 source_table 很大,則將 target_table 和 source_table 中的聯接資料欄定義為分佈索引鍵可以提高性能。
-
若要使用REMOVEDUPLICATES子句,您需要SELECTINSERT、和 Target _table 的DELETE權限。
範例
下列範例會建立兩個資料表,然後對其執行MERGE作業,更新目標資料表中的相符資料列,並插入不相符的資料列。然後它將另一個值插入到源表中並運行另一個MERGE操作,這次刪除匹配的行並從源表中插入新行。
首先建立並填入來源和目標資料表。
CREATE TABLE target (id INT, name CHAR(10)); CREATE TABLE source (id INT, name CHAR(10)); INSERT INTO target VALUES (101, 'Bob'), (102, 'John'), (103, 'Susan'); INSERT INTO source VALUES (102, 'Tony'), (103, 'Alice'), (104, 'Bill'); SELECT * FROM target; id | name -----+------------ 101 | Bob 102 | John 103 | Susan (3 rows) SELECT * FROM source; id | name -----+------------ 102 | Tony 103 | Alice 104 | Bill (3 rows)
接下來,將來源資料表合併到目標資料表,使用相符的資料列更新目標資料表,並從來源資料表插入不相符的資料列。
MERGE INTO target USING source ON target.id = source.id WHEN MATCHED THEN UPDATE SET id = source.id, name = source.name WHEN NOT MATCHED THEN INSERT VALUES (source.id, source.name); SELECT * FROM target; id | name -----+------------ 101 | Bob 102 | Tony 103 | Alice 104 | Bill (4 rows)
請注意,識別碼值為 102 和 103 的資料列會更新,以符合目標資料表中的名稱值。此外,ID 值為 104 且名稱值為 Bill 的新資料列會插入目標資料表中。
接下來,在來源資料表中插入新資料列。
INSERT INTO source VALUES (105, 'David'); SELECT * FROM source; id | name -----+------------ 102 | Tony 103 | Alice 104 | Bill 105 | David (4 rows)
最後,執行合併操作,刪除目標資料表中的相符資料列,並插入不相符的資料列。
MERGE INTO target USING source ON target.id = source.id WHEN MATCHED THEN DELETE WHEN NOT MATCHED THEN INSERT VALUES (source.id, source.name); SELECT * FROM target; id | name -----+------------ 101 | Bob 105 | David (2 rows)
識別碼值為 102、103 和 104 的資料列會從目標資料表中刪除,而識別碼值為 105 且名稱值為 David 的新資料列會插入目標資料表中。
下列範例顯示使用REMOVEDUPLICATES子句的MERGE命令。
CREATE TABLE target (id INT, name CHAR(10)); CREATE TABLE source (id INT, name CHAR(10)); INSERT INTO target VALUES (30, 'Tony'), (11, 'Alice'), (23, 'Bill'); INSERT INTO source VALUES (23, 'David'), (22, 'Clarence'); MERGE INTO target USING source ON target.id = source.id REMOVE DUPLICATES; SELECT * FROM target; id | name ---+------------ 30 | Tony 11 | Alice 23 | David 22 | Clarence (4 rows)
下列範例顯示使用REMOVEDUPLICATES子句的MERGE命令,如果 target_table 中有相符的資料列,則會從 target_table 移除重複的資料列。
CREATE TABLE target (id INT, name CHAR(10)); CREATE TABLE source (id INT, name CHAR(10)); INSERT INTO target VALUES (30, 'Tony'), (30, 'Daisy'), (11, 'Alice'), (23, 'Bill'), (23, 'Nikki'); INSERT INTO source VALUES (23, 'David'), (22, 'Clarence'); MERGE INTO target USING source ON target.id = source.id REMOVE DUPLICATES; SELECT * FROM target; id | name ---+------------ 30 | Tony 30 | Daisy 11 | Alice 23 | David 22 | Clarence (5 rows)
MERGE執行之後,目標資料表中只有一個識別碼值為 23 的資料列。由於 source_table 中沒有 ID 值為 30 的資料列,所以 ID 值為 30 的兩個重複資料列會保留在 target_table 中。