全部產品
Search

搜尋本產品

    MaxCompute：MERGE INTO

    文件中心

    MaxCompute：MERGE INTO

    更新時間：Dec 23, 2025

    當需要對Transactional表或Delta表執行INSERT、UPDATE、DELETE操作時，可以通過MERGE INTO功能將這些操作合并為一條SQL語句，根據與源表關聯的結果，對目標Transactional表執行插入、更新或刪除操作時，只需要執行一次全表掃描操作，以提高執行效率。

    功能介紹

    MaxCompute支援了DELETE、UPDATE功能，但當需要使用多個INSERT、UPDATE、DELETE對目標表進行大量操作時，需要編寫多條SQL語句，然後進行多次全表掃描才能完成操作。MaxCompute提供的MERGE INTO功能，只需要進行一次全表掃描操作，就可以完成全部操作，執行效率要高於INSERT+UPDATE+DELETE。

    MERGE INTO操作具備原子性，作業中的INSERT、UPDATE、DELETE操作都執行成功時，作業才算執行成功；任一內部邏輯處理失敗，則整體作業執行失敗。

    同時，MERGE INTO可以避免分別執行INSERT、UPDATE、DELETE操作時，可能導致部分操作執行成功，部分操作執行失敗，其中成功部分無法回退的問題。

    適用範圍

    • 執行MERGE INTO操作前需要具備目標Transactional表的讀取表資料許可權（Select）及更新表資料許可權（Update）。授權操作請參見MaxCompute許可權。

    • 不允許在同一條MERGE INTO語句中對相同的行執行多次INSERT或UPDATE操作。

    • MERGE INTO的目標表只支援事務表。

    • MERGE INTO語句中必須至少包含一個merge_action。

    • 多個not_matched_by_target_clause語句時，最多隻能有一個語句預設search_condition。

    • 多個not_matched_by_source_clause語句時，最多隻能有一個語句預設search_condition。

    • 多個matched_clause語句時，最多隻能有一個語句預設search_condition。

    • merge_condition和search_condition中不支援存在子查詢。

    命令格式

    MERGE INTO <target_table> AS <alias_name_t>
USING <source expression | table_name> AS <alias_name_s>
--從on開始對源表和目標表的資料進行關聯判斷。
ON merge_condition
{ merge_action } +

merge_action ::= matched_clause | not_matched_by_target_clause | not_matched_by_source_clause

--when matched…then指定on的結果為True的行為。多個when matched…then之間的資料無交集。
matched_clause ::= WHEN MATCHED [ AND <search_condition> ] THEN { merge_update_clause | merge_delete_clause }

--when not matched…then指定on的結果為False的行為。
not_matched_by_target_clause ::= WHEN NOT MATCHED [BY TARGET] [ AND <search_condition> ] THEN merge_insert_clause

not_matched_by_source_clause ::= WHEN NOT MATCHED BY SOURCE [ AND <search_condition> ] THEN  { merge_update_clause | merge_delete_clause }

merge_condition ::= BOOLEAN expression

search_condition ::= BOOLEAN expression

merge_update_clause ::= UPDATE SET <set_clause_list>

merge_delete_clause ::= DELETE

merge_insert_clause ::= INSERT <value_list> | ROW | *

    參數說明

    參數名

    是否必填

    描述

    target_table

    是

    目標表名稱，必須是實際存在的表。

    alias_name_t

    否

    目標表的別名。

    source expression|table_name

    是

    關聯的源表名稱、視圖或子查詢。

    alias_name_s

    否

    關聯的源表、視圖或子查詢的別名

    merge_condition

    是

    BOOLEAN類型判斷條件，判斷結果必須為True或False。

    search_condition

    否

    UPDATE、DELETE、INSERT操作相應的BOOLEAN類型判斷條件。需要注意：

    • 當出現三個WHEN子句時，UPDATE、DELETE、INSERT都只能出現一次。

    • 如果UPDATE和DELETE同時出現，出現在前的操作必須包括[AND <BOOLEAN expression>]。

    • WHEN NOT MATCHED只能出現在最後一個WHEN子句中。

    set_clause_list

    否

    待更新資料資訊。當出現UPDATE操作時必填。

    更多UPDATE資訊，請參見更新資料（UPDATE）。

    value_list

    否

    待插入資料資訊。當出現INSERT操作時必填。

    更多VALUES資訊，請參見VALUES。

    merge_action

    是

    merge_action有三種可選項：MATCHED、NOT MATCHED BY TARGET 和 NOT MATCHED BY SOURCE。

    每個merge_action可以包含一個可選的搜尋條件（search_condition）。只有當merge條件（merge_condition）和搜尋條件（search_condition）都滿足時，該 merge_action才會對某一行執行。當存在多個合格子句時，僅第一個合格merge_action會對該行執行。

    matched_clause

    否

    matched_clause有兩個選項：UPDATE和DELETE，用於定義當目標表中的某一行與源表中的某一行匹配時，如何更新或刪除該行。

    not_matched_by_target_clause

    否

    用於定義當源表中的某一行在目標表中沒有匹配行時，如何向目標表中插入該行。

    not_matched_by_source_clause

    否

    用於定義當目標表中的某一行在源表中沒有匹配行時，如何更新或刪除該行。

    merge_insert_clause

    否

    merge_insert_clause有三個可選項：value_list、ROW和*。value_list表示通過values運算式表示待插入資料；ROW和*都表示直接將源表匹配的資料插入到目標表，要求目標表和源表列個數一致，並且對應的類型是相容的。

    使用樣本

    • 樣本1：建立目標表acid_address_book_base1及源表tmp_table1，並插入資料。執行MERGE INTO操作，對符合ON條件的資料用源表的資料對目標表進行更新操作，對不符合ON條件並且源表中滿足_event_type_為I的資料插入目標表。命令樣本如下：

      --建立目標表acid_address_book_base1。
CREATE TABLE IF NOT EXISTS acid_address_book_base1 
(id BIGINT,first_name STRING,last_name STRING,phone STRING) 
PARTITIONED BY(year STRING, month STRING, day STRING, hour STRING) 
tblproperties ("transactional"="true"); 

--建立源表tmp_table1。
CREATE TABLE IF NOT EXISTS tmp_table1 
(id BIGINT, first_name STRING, last_name STRING, phone STRING, _event_type_ STRING);

--向目標表acid_address_book_base1插入測試資料。
INSERT OVERWRITE TABLE acid_address_book_base1 
PARTITION(year='2020', month='08', day='20', hour='16') 
VALUES (4, 'nihaho', 'li', '222'), (5, 'tahao', 'ha', '333'), 
(7, 'djh', 'hahh', '555');

--查詢目標表的資料確認插入測試資料的操作結果。
SET odps.sql.allow.fullscan=true;
SELECT * FROM acid_address_book_base1;
--返回結果
+------------+------------+------------+------------+------------+------------+------------+------------+
| id         | first_name | last_name  | phone      | year       | month      | day        | hour       |
+------------+------------+------------+------------+------------+------------+------------+------------+
| 4          | nihaho     | li         | 222        | 2020       | 08         | 20         | 16         |
| 5          | tahao      | ha         | 333        | 2020       | 08         | 20         | 16         |
| 7          | djh        | hahh       | 555        | 2020       | 08         | 20         | 16         |
+------------+------------+------------+------------+------------+------------+------------+------------+

--向源表tmp_table1插入測試資料。
INSERT OVERWRITE TABLE tmp_table1 VALUES 
(1, 'hh', 'liu', '999', 'I'), (2, 'cc', 'zhang', '888', 'I'),
(3, 'cy', 'zhang', '666', 'I'),(4, 'hh', 'liu', '999', 'U'),
(5, 'cc', 'zhang', '888', 'U'),(6, 'cy', 'zhang', '666', 'U');

--查詢源表的資料確認插入測試資料的操作結果。
SET odps.sql.allow.fullscan=true;
SELECT * FROM tmp_table1;
--返回結果
+------------+------------+------------+------------+--------------+
| id         | first_name | last_name  | phone      | _event_type_ |
+------------+------------+------------+------------+--------------+
| 1          | hh         | liu        | 999        | I            |
| 2          | cc         | zhang      | 888        | I            |
| 3          | cy         | zhang      | 666        | I            |
| 4          | hh         | liu        | 999        | U            |
| 5          | cc         | zhang      | 888        | U            |
| 6          | cy         | zhang      | 666        | U            |
+------------+------------+------------+------------+--------------+

--執行merge into操作。
MERGE INTO acid_address_book_base1 AS t USING tmp_table1 as s 
ON s.id = t.id AND t.year='2020' AND t.month='08' AND t.day='20' AND t.hour='16' 
WHEN MATCHED THEN UPDATE SET t.first_name = s.first_name, t.last_name = s.last_name, t.phone = s.phone 
WHEN NOT MATCHED AND (s._event_type_='I') THEN INSERT VALUES(s.id, s.first_name, s.last_name,s.phone,'2020','08','20','16');

--查詢目標表的資料確認merge into操作結果。
SET odps.sql.allow.fullscan=true;
SELECT * FROM acid_address_book_base1;
--返回結果
+------------+------------+------------+------------+------------+------------+------------+------------+
| id         | first_name | last_name  | phone      | year       | month      | day        | hour       |
+------------+------------+------------+------------+------------+------------+------------+------------+
| 4          | hh         | liu        | 999        | 2020       | 08         | 20         | 16         |
| 5          | cc         | zhang      | 888        | 2020       | 08         | 20         | 16         |
| 7          | djh        | hahh       | 555        | 2020       | 08         | 20         | 16         |
| 1          | hh         | liu        | 999        | 2020       | 08         | 20         | 16         |
| 2          | cc         | zhang      | 888        | 2020       | 08         | 20         | 16         |
| 3          | cy         | zhang      | 666        | 2020       | 08         | 20         | 16         |
+------------+------------+------------+------------+------------+------------+------------+------------+

    • 樣本2：建立目標表acid_target1及源表tmp_table2，並插入資料。執行MERGE INTO操作實現：對不符合ON條件並且源表中滿足name為'Nika'的資料插入目標表；對不符合ON條件並且目標表中滿足name為'Tommy'的資料刪除；對不符合ON條件並且目標表中滿足name為'Gene'的資料進行更新操作。

      --建立目標表acid_target1。
CREATE TABLE IF NOT EXISTS acid_target1 
(id BIGINT, name STRING, phone STRING)  
tblproperties ("transactional"="true"); 

--建立源表tmp_table2。
CREATE TABLE IF NOT EXISTS tmp_table2 
(id BIGINT, name STRING, phone STRING);

--向目標表acid_target1插入測試資料。
INSERT OVERWRITE TABLE acid_target1 
VALUES (1, 'Tommy', '111'), (2, 'Gene', '222'), 
(3, 'Abram', '333');

--查詢目標表的資料確認插入測試資料的操作結果。
SELECT * FROM acid_target1;
--返回結果
+------------+------+-------+
| id         | name | phone |
+------------+------+-------+
| 1          | Tommy | 111   |
| 2          | Gene | 222   |
| 3          | Abram | 333   |
+------------+------+-------+

--向源表tmp_table2插入測試資料。
INSERT OVERWRITE TABLE tmp_table2 
VALUES (6, 'Queen', '666'), (7, 'Nika', '777');

--查詢源表的資料確認插入測試資料的操作結果。
SELECT * FROM tmp_table2;
--返回結果
+------------+------+-------+
| id         | name | phone |
+------------+------+-------+
| 6          | Queen | 666   |
| 7          | Nika | 777   |
+------------+------+-------+

--執行merge into操作。
--將源表中name為'Nika'的資料插入目標表。
--將目標表中name為'Tommy'的資料刪除。
--對目標表中name為'Gene'的資料進行更新操作，將phone值從'222'更新為'999'。
MERGE INTO acid_target1 t
USING tmp_table2 s 
ON t.id = s.id
WHEN NOT MATCHED BY TARGET AND s.name = 'Nika'  THEN
  INSERT (id, name, phone) VALUES(s.id, s.name, s.phone)
WHEN NOT MATCHED BY SOURCE AND t.name = 'Tommy'  THEN
  DELETE
WHEN NOT MATCHED BY SOURCE AND t.name = 'Gene'  THEN
  UPDATE SET t.phone = '999';

--查詢目標表的資料確認merge into操作結果。
SELECT * FROM acid_target1;
--返回結果
+------------+------+-------+
| id         | name | phone |
+------------+------+-------+
| 2          | Gene | 999   |
| 7          | Nika | 777   |
| 3          | Abram | 333  |
+------------+------+-------+

    • 樣本3：建立目標表merge_acid_dp及源表merge_acid_source，並插入資料。以不指定分區方式執行MERGE INTO命令，進行更新或者插入資料，對目標表的所有分區生效。

      --建立目標表merge_acid_dp。
CREATE TABLE IF NOT EXISTS merge_acid_dp(c1 BIGINT NOT NULL, c2 BIGINT NOT NULL)
PARTITIONED BY (dd STRING, hh STRING) tblproperties ("transactional" = "true");
--建立源表merge_acid_source。
CREATE TABLE IF NOT EXISTS merge_acid_source(c1 BIGINT NOT NULL, c2 BIGINT NOT NULL,
  c3 STRING, c4 STRING) lifecycle 30;

--向目標表merge_acid_dp插入測試資料。
INSERT OVERWRITE TABLE merge_acid_dp PARTITION (dd='01', hh='01')
VALUES (1, 1), (2, 2);
INSERT OVERWRITE TABLE merge_acid_dp PARTITION (dd='02', hh='02')
VALUES (4, 1), (3, 2);

--查詢目標表的資料確認插入測試資料的操作結果。
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_dp;
--返回結果
+------------+------------+----+----+
| c1         | c2         | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 2          | 2          | 01 | 01 |
| 4          | 1          | 02 | 02 |
| 3          | 2          | 02 | 02 |
+------------+------------+----+----+

--向源表merge_acid_source插入測試資料。
INSERT OVERWRITE TABLE merge_acid_source VALUES(8, 2, '03', '03'),
(5, 5, '05', '05'), (6, 6, '02', '02');

--查詢源表的資料確認插入測試資料的操作結果。
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_source;
--返回結果
+------------+------------+----+----+
| c1         | c2         | c3 | c4 |
+------------+------------+----+----+
| 8          | 2          | 03 | 03 |
| 5          | 5          | 05 | 05 |
| 6          | 6          | 02 | 02 |
+------------+------------+----+----+

--執行merge into操作。
SET odps.sql.allow.fullscan=true;
MERGE INTO merge_acid_dp tar USING merge_acid_source src 
ON tar.c2 = src.c2 
WHEN MATCHED THEN 
UPDATE SET tar.c1 = src.c1 
WHEN NOT MATCHED THEN 
INSERT VALUES(src.c1, src.c2, src.c3, src.c4);

--查詢目標表的資料確認merge into操作結果。
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_dp;
--返回結果
+------------+------------+----+----+
| c1         | c2         | dd | hh |
+------------+------------+----+----+
| 6          | 6          | 02 | 02 |
| 5          | 5          | 05 | 05 |
| 8          | 2          | 02 | 02 |
| 8          | 2          | 01 | 01 |
| 1          | 1          | 01 | 01 |
| 4          | 1          | 02 | 02 |
+------------+------------+----+----+

    • 樣本4：建立目標表merge_acid_sp及源表merge_acid_source，並插入資料。以指定分區方式執行MERGE INTO命令，進行更新或者插入資料，對目標表的指定分區生效。

      --建立目標表merge_acid_sp。
CREATE TABLE IF NOT EXISTS merge_acid_sp(c1 BIGINT NOT NULL, c2 BIGINT NOT NULL)
PARTITIONED BY (dd STRING, hh STRING) tblproperties ("transactional" = "true");
--建立源表merge_acid_source。
CREATE TABLE IF NOT EXISTS merge_acid_source(c1 BIGINT NOT NULL, c2 BIGINT NOT NULL,
  c3 STRING, c4 STRING) lifecycle 30;

--向目標表merge_acid_sp插入測試資料。
INSERT OVERWRITE TABLE merge_acid_sp PARTITION (dd='01', hh='01')
VALUES (1, 1), (2, 2);
INSERT OVERWRITE TABLE merge_acid_sp PARTITION (dd='02', hh='02')
VALUES (4, 1), (3, 2);

--查詢目標表的資料確認插入測試資料的操作結果。
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_sp;
--返回結果
+------------+------------+----+----+
| c1         | c2         | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 2          | 2          | 01 | 01 |
| 4          | 1          | 02 | 02 |
| 3          | 2          | 02 | 02 |
+------------+------------+----+----+

--向源表merge_acid_source插入測試資料。
INSERT OVERWRITE TABLE merge_acid_source VALUES(8, 2, '03', '03'),
(5, 5, '05', '05'), (6, 6, '02', '02');

--查詢源表的資料確認插入測試資料的操作結果。
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_source;
--返回結果
+------------+------------+----+----+
| c1         | c2         | c3 | c4 |
+------------+------------+----+----+
| 8          | 2          | 03 | 03 |
| 5          | 5          | 05 | 05 |
| 6          | 6          | 02 | 02 |
+------------+------------+----+----+

--執行merge into操作，同時在on條件中指定只對目標表的dd = '01' and hh = '01'分區執行更新或者插入操作。
SET odps.sql.allow.fullscan=true;
MERGE INTO merge_acid_sp tar USING merge_acid_source src 
ON tar.c2 = src.c2 AND tar.dd = '01' AND tar.hh = '01' 
WHEN MATCHED THEN 
UPDATE SET tar.c1 = src.c1 
WHEN NOT MATCHED THEN 
INSERT VALUES(src.c1, src.c2, src.c3, src.c4);

--查詢目標表的資料確認merge into操作結果。
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_sp;
+------------+------------+----+----+
| c1         | c2         | dd | hh |
+------------+------------+----+----+
| 5          | 5          | 05 | 05 |
| 6          | 6          | 02 | 02 |
| 8          | 2          | 01 | 01 |
| 1          | 1          | 01 | 01 |
| 4          | 1          | 02 | 02 |
| 3          | 2          | 02 | 02 |
+------------+------------+----+----+

    • 樣本5：建立Delta Table類型目標表mf_tt6及源表mf_delta，並插入資料。以指定分區方式執行MERGE INTO命令，進行更新、插入或刪除資料，對目標表的指定分區生效。 

      --建立Transaction Table2.0類型目標表mf_tt6。
CREATE TABLE IF NOT EXISTS mf_tt6 (pk BIGINT NOT NULL PRIMARY key, 
                  val BIGINT NOT NULL) 
                  PARTITIONED BY (dd STRING, hh STRING) 
                  tblproperties ("transactional"="true");
--向目標表mf_tt6插入測試資料。
INSERT OVERWRITE TABLE mf_tt6 PARTITION (dd='01', hh='02') VALUES (1, 1), (2, 2), (3, 3);
INSERT OVERWRITE TABLE mf_tt6 PARTITION (dd='01', hh='01') VALUES (1, 10), (2, 20), (3, 30);

--開啟全表掃描，僅此Session有效。執行select語句查看錶mf_tt6中的資料。
SET odps.sql.allow.fullscan=true;
SELECT * FROM mf_tt6;
--返回結果
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 10         | 01 | 01 |
| 3          | 30         | 01 | 01 |
| 2          | 20         | 01 | 01 |
| 1          | 1          | 01 | 02 |
| 3          | 3          | 01 | 02 |
| 2          | 2          | 01 | 02 |
+------------+------------+----+----+

--建立源表mf_delta，並插入測試資料。
CREATE TABLE IF NOT EXISTS mf_delta AS SELECT pk, val FROM VALUES (1, 10), (2, 20), (6, 60) t (pk, val);

--查詢源表的資料，確認插入測試資料的操作結果。
SELECT * FROM mf_delta;
--返回結果
+------+------+
| pk   | val  |
+------+------+
| 1    | 10   |
| 2    | 20   |
| 6    | 60   |
+------+------+

--執行merge into操作，同時在on條件中指定只對目標表mf_tt6的dd = '01' and hh = '02'分區執行更新、插入或刪除操作。

MERGE INTO mf_tt6 USING mf_delta 
ON mf_tt6.pk = mf_delta.pk AND mf_tt6.dd='01' AND mf_tt6.hh='02' 
WHEN MATCHED AND (mf_tt6.pk > 1) THEN 
UPDATE SET mf_tt6.val = mf_delta.val 
WHEN MATCHED THEN DELETE 
WHEN NOT MATCHED THEN 
INSERT VALUES (mf_delta.pk, mf_delta.val, '01', '02');         

--查詢目標表的資料確認merge into操作結果。
SET odps.sql.allow.fullscan=true;
SELECT * FROM mf_tt6;
--返回結果
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 10         | 01 | 01 |
| 3          | 30         | 01 | 01 |
| 2          | 20         | 01 | 01 |
| 3          | 3          | 01 | 02 |
| 6          | 60         | 01 | 02 |
| 2          | 20         | 01 | 02 |
+------------+------------+----+----+