すべてのプロダクト
Search

このプロダクト

    MaxCompute:MERGE INTO

    ドキュメントセンター

    MaxCompute:MERGE INTO

    最終更新日:Jan 24, 2025

    トランザクションテーブルまたはDeltaテーブルに対してINSERT、UPDATE、およびDELETE操作を実行する必要がある場合は、操作を1つのMERGE INTOステートメントにカプセル化できます。 次に、MERGE INTOステートメントを実行して、ソーステーブルとの結合条件に基づいてテーブルに対してこれらの操作を実行できます。 このようにして、テーブル内のすべてのデータが1回だけスキャンされ、実行効率が向上します。

    前提条件

    MERGE INTO操作を実行するには、移行先トランザクションテーブルに対する [選択] および [更新] 権限が必要です。 権限を付与する方法の詳細については、「MaxCompute権限」をご参照ください。

    説明

    MaxComputeでは、テーブルに対してDELETEまたはUPDATE操作を実行して、テーブルからデータを削除またはテーブル内のデータを更新できます。 テーブルに対してINSERT、UPDATE、およびDELETE操作を同時に実行する場合は、操作ごとにステートメントを記述して実行する必要があります。 この場合、フルテーブルスキャン操作は複数回実行されます。 MaxComputeでは、実行効率を向上させるために、MERGE INTOステートメントを実行して、テーブルに対してINSERT、UPDATE、およびDELETE操作を同時に実行できます。 このように、フルテーブルスキャン操作は1回だけ実行されます。 この方法は、INSERT、UPDATE、およびDELETE操作を個別に実行する方法よりも効率的です。

    MERGE INTOステートメントは、操作の原子性を保証します。 ジョブは、ジョブ内のすべてのINSERT、UPDATE、およびDELETE操作が成功した後にのみ成功します。 操作が失敗した場合、ジョブも失敗します。

    INSERT、UPDATE、およびDELETE操作を個別に実行すると、特定の操作が失敗する可能性があります。 正常な操作が実行されたデータは復元できません。 この問題を防ぐには、MERGE INTOステートメントを実行します。

    制限事項

    単一のMERGE INTOステートメントを使用して、テーブル内の同じ行に対して複数のINSERTまたはUPDATE操作を実行することはできません。

    構文

    MERGE INTO <target_table> AS <alias_name_t> USING <source expression|table_name> AS <alias_name_s>
-- The ON clause specifies the JOIN conditions of the source table and the destination table. 
ON <BOOLEAN expression1>
-- The WHEN MATCHED...THEN clause specifies the operation to be performed when the result of the ON clause is True. The operations of multiple WHEN MATCHED...THEN clauses cannot be performed on the same data. 
WHEN matched [AND <BOOLEAN expression2>] THEN UPDATE SET <set_clause_list>
WHEN matched [AND <BOOLEAN expression3>] THEN DELETE
-- The WHEN MATCHED...THEN clause specifies the operation to be performed when the result of the ON clause is False. 
WHEN NOT matched [AND <BOOLEAN expression4>] THEN INSERT VALUES <value_list>

    Parameters

    パラメーター

    必須 / 任意

    説明

    target_table

    対象

    ターゲットテーブルの名前。既存のテーブルである必要があります。

    alias_name_t

    非対象

    宛先テーブルのエイリアス。

    source expression|table_name

    対象

    ターゲットテーブルと結合するソーステーブル、ビュー、またはサブクエリの名前。

    alias_name_s

    非対象

    ターゲットテーブルと結合するソーステーブル、ビュー、またはサブクエリのエイリアス。

    BOOLEAN expression1

    対象

    BOOLEAN型の値を返す条件。 値はTrueまたはFalseでなければなりません。

    BOOLEAN expression2、BOOLEAN expression3、BOOLEAN expression4

    非対象

    実行するUPDATE、DELETE、およびINSERT操作のそれぞれに条件を指定できます。 条件はBOOLEAN型の値を返す必要があります。 以下の点にご注意ください。

    • MERGE INTOステートメントに3つのWHEN句がある場合、UPDATE、DELETE、INSERTの各操作は1回だけ実行できます。

    • MERGE INTOステートメントにUPDATE操作とDELETE操作の両方が含まれている場合は、最初に実行する必要がある操作に対して [and <boolean expression>] 条件を指定する必要があります。

    • WHEN NOT MATCHED句は、最後のWHEN句としてのみ使用でき、INSERT操作のみをサポートします。

    set_clause_list

    非対象

    UPDATE操作を実行する場合は、このパラメーターが必要です。 このパラメータは、更新するデータを指定します。 UPDATEの詳細については、「UPDATE and DELETE」の「UPDATE」セクションをご参照ください。

    value_list

    非対象

    INSERT操作を実行する場合は、このパラメーターが必要です。 このパラメータは、挿入するデータを指定します。 valuesの詳細については、「VALUES」をご参照ください。

    例

    • 例1: acid_address_book_base1という名前のターゲットテーブルとtmp_table1という名前のソーステーブルを作成し、テーブルにデータを挿入します。 ターゲットテーブルでMERGE INTOステートメントを実行します。 指定されたON結合条件を満たすソーステーブルのデータエントリを使用して、宛先テーブルの結合されたデータエントリを更新します。 指定されたON結合条件を満たさず、event_type列の値がIであるデータエントリが宛先テーブルに挿入されます。 サンプル文:

      -- Create a destination table named 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"); 

-- Create a source table named tmp_table1. 
CREATE TABLE IF NOT EXISTS tmp_table1 
(id BIGINT, first_name STRING, last_name STRING, phone STRING, _event_type_ STRING);

-- Insert test data into the acid_address_book_base1 table. 
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');

-- Query data from the destination table and check whether the INSERT operation is successfully performed. 
SET odps.sql.allow.fullscan=true;
SELECT * FROM acid_address_book_base1;
-- Return results
+------------+------------+------------+------------+------------+------------+------------+------------+
| 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         |
+------------+------------+------------+------------+------------+------------+------------+------------+

-- Insert test data into the tmp_table1 table. 
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');

-- Query data from the source table and check whether the INSERT operation is successfully performed. 
SET odps.sql.allow.fullscan=true;
SELECT * FROM tmp_table1;
-- Return results
+------------+------------+------------+------------+--------------+
| 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            |
+------------+------------+------------+------------+--------------+

-- Execute the MERGE INTO statement. 
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');

-- Query data from the destination table and check whether the MERGE INTO operation is successfully performed. 
SET odps.sql.allow.fullscan=true;
SELECT * FROM acid_address_book_base1;
-- Return results
+------------+------------+------------+------------+------------+------------+------------+------------+
| 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: merge_acid_dpという名前のターゲットテーブルとmerge_acid_sourceという名前のソーステーブルを作成します。 テーブルにデータを挿入します。 次に、パーティションを指定せずにMERGE INTOステートメントを実行して、ターゲットテーブルのデータを更新または挿入します。 MERGE INTO操作は、すべてのパーティションで有効になります。 

      -- Create a destination table named 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");
-- Create a source table named 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;

-- Insert test data into the destination table 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);

-- Query data from the destination table and check whether the INSERT operation is successfully performed. 
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_dp;
-- Return results
+------------+------------+----+----+
| c1         | c2         | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 2          | 2          | 01 | 01 |
| 4          | 1          | 02 | 02 |
| 3          | 2          | 02 | 02 |
+------------+------------+----+----+

-- Insert test data into the source table merge_acid_source. 
INSERT OVERWRITE TABLE merge_acid_source VALUES(8, 2, '03', '03'),
(5, 5, '05', '05'), (6, 6, '02', '02');

-- Query data from the source table to check whether the INSERT operation is successfully performed. 
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_source;
-- Return results
+------------+------------+----+----+
| c1         | c2         | c3 | c4 |
+------------+------------+----+----+
| 8          | 2          | 03 | 03 |
| 5          | 5          | 05 | 05 |
| 6          | 6          | 02 | 02 |
+------------+------------+----+----+

-- Execute the MERGE INTO statement. 
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);

-- Query data from the destination table to check whether the MERGE INTO operation is successfully performed. 
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_dp;
-- Return results
+------------+------------+----+----+
| 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 |
+------------+------------+----+----+

    • 例3: merge_acid_spという名前のターゲットテーブルとmerge_acid_sourceという名前のソーステーブルを作成します。 テーブルにデータを挿入します。 MERGE INTOステートメントを実行して、ターゲットテーブルの指定されたパーティションにデータを更新または挿入します。 

      -- Create a destination table named 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");
-- Create a source table named 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;

-- Insert test data into the destination table 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);

-- Query data from the destination table and check whether the INSERT operation is successfully performed. 
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_sp;
-- Return results
+------------+------------+----+----+
| c1         | c2         | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 2          | 2          | 01 | 01 |
| 4          | 1          | 02 | 02 |
| 3          | 2          | 02 | 02 |
+------------+------------+----+----+

-- Insert test data into the source table merge_acid_source. 
INSERT OVERWRITE TABLE merge_acid_source VALUES(8, 2, '03', '03'),
(5, 5, '05', '05'), (6, 6, '02', '02');

-- Query data from the source table to check whether the INSERT operation is successfully performed. 
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_source;
-- Return results
+------------+------------+----+----+
| c1         | c2         | c3 | c4 |
+------------+------------+----+----+
| 8          | 2          | 03 | 03 |
| 5          | 5          | 05 | 05 |
| 6          | 6          | 02 | 02 |
+------------+------------+----+----+

-- Specify the dd = '01' and hh = '01' partitions in the ON clause of the MERGE INTO statement and execute the statement to update or insert data in the specified partitions of the destination table. 
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);

-- Query data from the destination table to check whether the MERGE INTO operation is successfully performed. 
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 |
+------------+------------+----+----+

    • 例4: ターゲットテーブルとしてmf_tt6という名前のDeltaテーブルを作成し、mf_deltaという名前のソーステーブルを作成します。 ソーステーブルと宛先テーブルにデータを挿入します。 MERGE INTOステートメントでパーティションを指定し、ステートメントを実行して、ターゲットテーブルの指定されたパーティションに対してUPDATE、INSERT、またはDELETE操作を実行します。 

      -- Create a Delta table named mf_tt6 as the destination table. 
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");
-- Insert test data into the destination table 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);

-- Enable a full table scan only for the current session. Execute the SELECT statement to query data from the mf_tt6 table. 
SET odps.sql.allow.fullscan=true;
SELECT * FROM mf_tt6;
-- Return results
+------------+------------+----+----+
| 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 |
+------------+------------+----+----+

-- Create a source table named mf_delta and insert test data into the table. 
CREATE TABLE IF NOT EXISTS mf_delta AS SELECT pk, val FROM VALUES (1, 10), (2, 20), (6, 60) t (pk, val);

-- Query data from the source table and confirm the result of the INSERT operation. 
SELECT * FROM mf_delta;
-- Return results
+------+------+
| pk   | val  |
+------+------+
| 1    | 10   |
| 2    | 20   |
| 6    | 60   |
+------+------+

-- Specify the dd = '01' and hh = '02' partitions in the ON clause of the MERGE INTO statement and execute the statement to perform the UPDATE, INSERT, or DELETE operation on the specified partitions in the destination table mf_tt6. 
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');         

-- Query data from the destination table and check whether the MERGE INTO operation is successfully performed. 
SET odps.sql.allow.fullscan=true;
SELECT * FROM mf_tt6;
-- Return results
+------------+------------+----+----+
| 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 |
+------------+------------+----+----+