カテゴリ:

DML コマンド - 一般

MERGE

2番目のテーブルまたはサブクエリの値に基づいて、テーブルの値を挿入、更新、削除します。これは、2番目のテーブルが、ターゲットテーブルに新しい行(挿入)、変更された行(更新)、および/またはマークされた行(削除)を含む変更ログである場合に役立ちます。

このコマンドは、次のケースを処理するためのセマンティクスをサポートしています。

  • 一致する値(更新および削除用)。

  • 一致しない値(挿入用)。

こちらもご参照ください:

DELETE , UPDATE

このトピックの内容:

構文

MERGE INTO <target_table> USING <source> ON <join_expr> { matchedClause | notMatchedClause } [ ... ]

条件:

matchedClause ::=
  WHEN MATCHED [ AND <case_predicate> ] THEN { UPDATE SET <col_name> = <expr> [ , <col_name2> = <expr2> ... ] | DELETE } [ ... ]
notMatchedClause ::=
   WHEN NOT MATCHED [ AND <case_predicate> ] THEN INSERT [ ( <col_name> [ , ... ] ) ] VALUES ( <expr> [ , ... ] )

パラメーター

ターゲットテーブル

マージするテーブルを指定します。

ソース

ターゲットテーブルと結合するテーブルまたはサブクエリを指定します。

結合式

ターゲットテーブルとソースを結合する式を指定します。

matchedClause (更新または削除用)

WHEN MATCHED ... THEN UPDATE <col_name> = <expr> | DELETE

値が一致したときに実行するアクションを指定します。

AND ケース述語

オプションで、trueの場合に一致するケースを実行する式を指定します。

デフォルト: 値なし(一致するケースが常に実行されます)

SET 列名 = [ ... ]

更新または挿入するターゲットテーブル内の列と、新しい列値に対応する式を指定します(ターゲットとソースの両方のリレーションを参照できます)。

単一の SET 副次句では、更新/削除する複数の列を指定できます。

notMatchedClause (挿入用)

WHEN NOT MATCHED ... THEN INSERT

値が一致しない場合に実行するアクションを指定します。

AND ケース述語

オプションで、trueの場合、一致しないケースが実行される式を指定します。

デフォルト: 値なし(一致しないケースが常に実行されます)

( 列名 [ , ... ] )

オプションで、更新または挿入するターゲットテーブル内の1つ以上の列を指定します。

デフォルト: 値なし(ターゲットテーブル内のすべての列が更新または挿入されます)

VALUES ( [ , ... ] )

挿入された列の値に対応する式を指定します(ソースリレーションを参照する必要があります)。

使用上の注意

  • 単一の MERGE ステートメントには、複数の一致する句と一致しない句(つまり、 WHEN MATCHED ...WHEN NOT MATCHED ...)を含めることができます。

  • AND 副次句を省略する一致または不一致の句(デフォルトの動作)は、ステートメント内のその句タイプの 最後 でなければなりません(例えば、 WHEN MATCHED ... 句の後に WHEN MATCHED AND ... 句を続けることはできません)。これを行うと、到達不能なケースが発生し、エラーが返されます。

結合動作の複製

UPDATE と DELETE の非決定的結果

マージがソースの複数の行に対してターゲットテーブルの行を結合する場合、次の結合条件は非決定的な結果を生成します(つまり、システムはターゲット行の更新または削除に使用するソース値を決定できません)。

  • ターゲット行が選択され、複数の値で更新されます(例: WHEN MATCHED ... THEN UPDATE)。

  • ターゲット行が選択され、更新と削除の両方が行われます(例: WHEN MATCHED ... THEN UPDATEWHEN MATCHED ... THEN DELETE)。

この状況では、マージの結果は ERROR_ON_NONDETERMINISTIC_MERGE セッションパラメーターに指定された値に依存します。

  • TRUE (デフォルト値)の場合、マージはエラーを返します。

  • FALSE の場合、複製の中から1つの行が選択されて、更新または削除が実行されます。選択された行は定義されていません。

UPDATE の決定的な結果と DELETE

確定的マージは常にエラーなしで完了します。各ターゲット行について次の条件を満たす場合、マージは決定的です。

  • 1つ以上のソース行が WHEN MATCHED ... THEN DELETE 句を満たし、その他のソース行が WHEN MATCHED 句を満たさない

    OR

  • 正確に1つのソース行が WHEN MATCHED ... THEN UPDATE 句を満たし、および他のソース行は WHEN MATCHED 句を満たさない。

これにより、 MERGE は UPDATE および DELETE コマンドと意味的に同等になります。

注釈

データソース(つまり、ソーステーブルまたはサブクエリ)の複数の行が ON 条件に基づいてターゲットテーブルと一致する場合のエラーを回避するには、ソース句で GROUP BY を使用して、各ターゲット行がソース内の(最大)1つの行に対して結合されるようにします。

次の例では、 src に同じ k 値を持つ複数の行が含まれると仮定します。ターゲット行の行を同じ値 k で更新するためにどの値(v)が使用されるかはあいまいです。MAX()および GROUP BY を使用することにより、クエリは srcv のどの値が使用されているかを明確に示します。

MERGE INTO target USING (select k, max(v) as v from src group by k) AS b ON target.k = b.k
  WHEN MATCHED THEN UPDATE SET target.v = b.v
  WHEN NOT MATCHED THEN INSERT (k, v) VALUES (b.k, b.v);

INSERT の決定的な結果

確定的マージは常にエラーなしで完了します。

MERGE に WHEN NOT MATCHED ... THEN INSERT 句が含まれ、ターゲットに一致する行がない場合、およびソースに重複する値が含まれる場合、ターゲットはソースの each コピーの行のコピーを1つ取得します。(例は以下に含まれています。)

簡単なマージを実行します。

テーブルを作成してロードします。

CREATE TABLE target_table (ID INTEGER, description VARCHAR);

CREATE TABLE source_table (ID INTEGER, description VARCHAR);
INSERT INTO target_table (ID, description) VALUES
    (10, 'To be updated (this is the old value)')
    ;

INSERT INTO source_table (ID, description) VALUES
    (10, 'To be updated (this is the new value)')
    ;

MERGE ステートメントを実行します。

MERGE INTO target_table USING source_table 
    ON target_table.id = source_table.id
    WHEN MATCHED THEN 
        UPDATE SET target_table.description = source_table.description;
+------------------------+
| number of rows updated |
|------------------------|
|                      1 |
+------------------------+

ターゲットテーブルに新しい値を表示します(ソーステーブルは変更されません)。

SELECT * FROM target_table;
+----+---------------------------------------+
| ID | DESCRIPTION                           |
|----+---------------------------------------|
| 10 | To be updated (this is the new value) |
+----+---------------------------------------+
SELECT * FROM source_table;
+----+---------------------------------------+
| ID | DESCRIPTION                           |
|----+---------------------------------------|
| 10 | To be updated (this is the new value) |
+----+---------------------------------------+

操作(削除、更新、挿入)を組み合わせて基本的なマージを実行します。

MERGE INTO t1 USING t2 ON t1.t1Key = t2.t2Key
    WHEN MATCHED AND t2.marked = 1 THEN DELETE
    WHEN MATCHED AND t2.isNewStatus = 1 THEN UPDATE SET val = t2.newVal, status = t2.newStatus
    WHEN MATCHED THEN UPDATE SET val = t2.newVal
    WHEN NOT MATCHED THEN INSERT (val, status) VALUES (t2.newVal, t2.newStatus);

ソースに重複する値があり、ターゲットに一致する値がないマージを実行します。ソースレコードのすべてのコピーがターゲットに挿入されることに注意してください。

両方のテーブルを切り捨て、新しい行をソーステーブルにロードします。行には重複が含まれていることに注意してください。

TRUNCATE TABLE source_table;

TRUNCATE TABLE target_table;

INSERT INTO source_table (ID, description) VALUES
    (50, 'This is a duplicate in the source and has no match in target'),
    (50, 'This is a duplicate in the source and has no match in target')
    ;

MERGE ステートメントを実行します。

MERGE INTO target_table USING source_table 
    ON target_table.id = source_table.id
    WHEN MATCHED THEN 
        UPDATE SET target_table.description = source_table.description
    WHEN NOT MATCHED THEN 
        INSERT (ID, description) VALUES (source_table.id, source_table.description);
+-------------------------+------------------------+
| number of rows inserted | number of rows updated |
|-------------------------+------------------------|
|                       2 |                      0 |
+-------------------------+------------------------+

ターゲットテーブルに新しい値を表示します。

SELECT ID FROM target_table;
+----+
| ID |
|----|
| 50 |
| 50 |
+----+

非決定的および決定的結果を生成する結合を使用してレコードをマージします。

-- Setup for example.
CREATE TABLE target_orig (k NUMBER, v NUMBER);
INSERT INTO target_orig VALUES (0, 10);

CREATE TABLE src (k NUMBER, v NUMBER);
INSERT INTO src VALUES (0, 11), (0, 12), (0, 13);

-- Multiple updates conflict with each other.
-- If ERROR_ON_NONDETERMINISTIC_MERGE=true, returns an error;
-- otherwise updates target.v with a value (e.g. 11, 12, or 13) from one of the duplicate rows (row not defined).

CREATE OR REPLACE TABLE target CLONE target_orig;

MERGE INTO target
  USING src ON target.k = src.k
  WHEN MATCHED THEN UPDATE SET target.v = src.v;

-- Updates and deletes conflict with each other.
-- If ERROR_ON_NONDETERMINISTIC_MERGE=true, returns an error;
-- otherwise either deletes the row or updates target.v with a value (e.g. 12 or 13) from one of the duplicate rows (row not defined).

CREATE OR REPLACE TABLE target CLONE target_orig;

MERGE INTO target
  USING src ON target.k = src.k
  WHEN MATCHED AND src.v = 11 THEN DELETE
  WHEN MATCHED THEN UPDATE SET target.v = src.v;

-- Multiple deletes do not conflict with each other;
-- joined values that do not match any clause do not prevent the delete (src.v = 13).
-- Merge succeeds and the target row is deleted.

CREATE OR REPLACE TABLE target CLONE target_orig;

MERGE INTO target
  USING src ON target.k = src.k
  WHEN MATCHED AND src.v <= 12 THEN DELETE;

-- Joined values that do not match any clause do not prevent an update (src.v = 12, 13).
-- Merge succeeds and the target row is set to target.v = 11.

CREATE OR REPLACE TABLE target CLONE target_orig;

MERGE INTO target
  USING src ON target.k = src.k
  WHEN MATCHED AND src.v = 11 THEN UPDATE SET target.v = src.v;

-- Use GROUP BY in the source clause to ensure that each target row joins against one row
-- in the source:

CREATE OR REPLACE TABLE target CLONE target_orig;

MERGE INTO target USING (select k, max(v) as v from src group by k) AS b ON target.k = b.k
  WHEN MATCHED THEN UPDATE SET target.v = b.v
  WHEN NOT MATCHED THEN INSERT (k, v) VALUES (b.k, b.v);

次の例では、 members テーブルに、地元のジムに支払われた名前、住所、および現在の料金(members.fee)が格納されています。 signup テーブルには、各メンバーの登録日(signup.date)が保存されます。MERGE ステートメントは、無料トライアルの期限が切れた後、30日以上前にジムに参加したメンバーに標準の$40料金を適用します。

MERGE INTO members m
  USING (
  SELECT id, date
  FROM signup
  WHERE DATEDIFF(day, CURRENT_DATE(), signup.date::DATE) < -30) s ON m.id = s.id
  WHEN MATCHED THEN UPDATE SET m.fee = 40;