MERGE¶

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

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

一致する値（更新および削除用）。
一致しない値（挿入用）

こちらもご参照ください。: DELETE , UPDATE

構文¶

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

Copy

条件：

matchedClause ::=
  WHEN MATCHED
    [ AND <case_predicate> ]
    THEN { UPDATE { ALL BY NAME | SET <col_name> = <expr> [ , <col_name> = <expr> ... ] } | DELETE } [ ... ]

Copy

notMatchedClause ::=
   WHEN NOT MATCHED
     [ AND <case_predicate> ]
     THEN INSERT { ALL BY NAME | [ ( <col_name> [ , ... ] ) ] VALUES ( <expr> [ , ... ] ) }

Copy

パラメーター¶

target_table: マージするテーブルを指定します。
source: ターゲットテーブルと結合するテーブルまたはサブクエリを指定します。
join_expr: ターゲットテーブルとソースを結合する式を指定します。

`matchedClause` （更新または削除用）¶

WHEN MATCHED ... AND case_predicate

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

デフォルト：値なし（一致するケースが常に実行されます）

WHEN MATCHED ... THEN { UPDATE { ALL BY NAME | SET ... } | DELETE }

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

ALL BY NAME

ターゲットテーブルのすべての列をソースからの値で更新します。ターゲットテーブルの各列は、ソースの同名の列の値で更新されます。

ターゲットテーブルとソースは、同じ数の列を持ち、すべての列に同じ名前を付ける必要があります。ただし、列の順序はターゲットテーブルとソーステーブルで異なる場合があります。

SET col_name = expr [ , col_name = expr ... ]

新しい列の値に対応する式を使用して、ターゲットテーブルの指定された列を更新します（ターゲットとソースの両方の関係を参照できます）

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

DELETE

ターゲットテーブルの行がソースと一致した場合に、その行を削除します。

`notMatchedClause` （挿入用）¶

WHEN NOT MATCHED ... AND case_predicate

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

デフォルト：値なし（一致しないケースが常に実行されます）

WHEN NOT MATCHED ... THEN INSERT . { ALL BY NAME | [ ( col_name [ , ... ] ) ] VALUES ( expr [ , ... ] ) }

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

ALL BY NAME

ターゲットテーブルのすべての列にソースからの値を挿入します。ターゲットテーブルの各列には、ソースから同名の列の値が挿入されます。

( col_name [ , ... ] )

オプションで、ソースからの値を挿入するターゲットテーブルの1つ以上の列を指定します。

デフォルト: 値なし（ターゲットテーブルの列がすべて挿入されます）

VALUES ( expr [ , ... ] )

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

使用上の注意¶

単一の MERGE ステートメントには、複数の一致する句と一致しない句（つまり、 WHEN MATCHED ... と WHEN NOT MATCHED ...）を含めることができます。
AND 副次句を省略する一致または不一致の句（デフォルトの動作）は、ステートメント内のその句型の最後でなければなりません（例えば、 WHEN MATCHED ... 句の後に WHEN MATCHED AND ... 句を続けることはできません）これを行うと、到達不能なケースが発生し、エラーが返されます。

結合動作を複製する¶

ソーステーブルの複数行がターゲットテーブルの1行と一致する場合、結果は決定論的または非決定論的となります。このセクションでは、これらのユースケースにおける MERGE の動作について説明します。

UPDATE と DELETE の非決定的結果¶

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

ターゲット行が選択され、複数の値で更新されます（例: WHEN MATCHED ... THEN UPDATE）
ターゲット行が選択され、更新と削除の両方が行われます（例: WHEN MATCHED ... THEN UPDATE 、 WHEN MATCHED ... THEN DELETE）

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

TRUE （デフォルト値）の場合、マージはエラーを返します。
FALSE の場合、複製の中から1つの行が選択されて、更新または削除が実行されます。選択された行は定義されていません。

UPDATE と DELETE の決定的な結果¶

確定的マージは常にエラーなしで完了します。マージは、各ターゲット行に対して次の条件の*少なくとも1つ*を満たす場合、決定論的になります。

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

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

注釈

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

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

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);

Copy

INSERT の決定的な結果¶

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

MERGE ステートメントに WHEN NOT MATCHED ... THEN INSERT 句が含まれ、ターゲットに一致する行がない場合、およびソースに重複する値が含まれる場合、ターゲットはソース内の*各*コピーに対して行のコピーを1つ取得します。例については、重複ソースとマージを実行するをご参照ください。

例¶

以下の例では、 MERGE コマンドを使用しています。

値を更新する基本的なマージを実行する
複数の操作による基本的なマージを実行する
ALL BY NAME を使用してマージを実行します
重複ソースとマージを実行する
決定論的結果と非決定論的結果とのマージを実行する
DATE の値に基づいてマージを実行する

値を更新する基本的なマージを実行する¶

以下の例では、ソーステーブルの値を使用してターゲットテーブルの値を更新する基本的なマージを実行します。2つのテーブルを作成し、ロードします。

CREATE OR REPLACE TABLE merge_example_target (id INTEGER, description VARCHAR);

INSERT INTO merge_example_target (id, description) VALUES
  (10, 'To be updated (this is the old value)');

CREATE OR REPLACE TABLE merge_example_source (id INTEGER, description VARCHAR);

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

Copy

テーブルの値を表示します。

SELECT * FROM merge_example_target;

Copy

+----+---------------------------------------+
| ID | DESCRIPTION                           |
|----+---------------------------------------|
| 10 | To be updated (this is the old value) |
+----+---------------------------------------+

SELECT * FROM merge_example_source;

Copy

+----+---------------------------------------+
| ID | DESCRIPTION                           |
|----+---------------------------------------|
| 10 | To be updated (this is the new value) |
+----+---------------------------------------+

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

MERGE INTO merge_example_target
  USING merge_example_source
  ON merge_example_target.id = merge_example_source.id
  WHEN MATCHED THEN
    UPDATE SET merge_example_target.description = merge_example_source.description;

Copy

+------------------------+
| number of rows updated |
|------------------------|
|                      1 |
+------------------------+

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

SELECT * FROM merge_example_target;

Copy

+----+---------------------------------------+
| ID | DESCRIPTION                           |
|----+---------------------------------------|
| 10 | To be updated (this is the new value) |
+----+---------------------------------------+

SELECT * FROM merge_example_source;

Copy

+----+---------------------------------------+
| ID | DESCRIPTION                           |
|----+---------------------------------------|
| 10 | To be updated (this is the new value) |
+----+---------------------------------------+

複数の操作による基本的なマージを実行する¶

複数のオペレーション（INSERT 、UPDATE 、DELETE）を組み合わせた基本的なマージを行います。

2つのテーブルを作成し、ロードします。

CREATE OR REPLACE TABLE merge_example_mult_target (
  id INTEGER,
  val INTEGER,
  status VARCHAR);

INSERT INTO merge_example_mult_target (id, val, status) VALUES
  (1, 10, 'Production'),
  (2, 20, 'Alpha'),
  (3, 30, 'Production');

CREATE OR REPLACE TABLE merge_example_mult_source (
  id INTEGER,
  marked VARCHAR,
  isnewstatus INTEGER,
  newval INTEGER,
  newstatus VARCHAR);

INSERT INTO merge_example_mult_source (id, marked, isnewstatus, newval, newstatus) VALUES
  (1, 'Y', 0, 10, 'Production'),
  (2, 'N', 1, 50, 'Beta'),
  (3, 'N', 0, 60, 'Deprecated'),
  (4, 'N', 0, 40, 'Production');

Copy

テーブルの値を表示します。

SELECT * FROM merge_example_mult_target;

Copy

+----+-----+------------+
| ID | VAL | STATUS     |
|----+-----+------------|
|  1 |  10 | Production |
|  2 |  20 | Alpha      |
|  3 |  30 | Production |
+----+-----+------------+

SELECT * FROM merge_example_mult_source;

Copy

+----+--------+-------------+--------+------------+
| ID | MARKED | ISNEWSTATUS | NEWVAL | NEWSTATUS  |
|----+--------+-------------+--------+------------|
|  1 | Y      |           0 |     10 | Production |
|  2 | N      |           1 |     50 | Beta       |
|  3 | N      |           0 |     60 | Deprecated |
|  4 | N      |           0 |     40 | Production |
+----+--------+-------------+--------+------------+

以下のマージ例は、merge_example_mult_target テーブルに対して以下のアクションを実行します。

merge_example_mult_source で同じ id を持つ行の marked 列が Y になっているため、id が 1 に設定された行を削除します。
merge_example_mult_source 内の同じ行の isnewstatus が 1 に設定されているため id が 2 に設定されている行の val 値と status 値を、merge_example_mult_source 内の同じ id を持つ行の値で更新します。
id が 3 に設定された行の val 値を、merge_example_mult_source 内の同じ id を持つ行の値で更新します。merge_example_mult_source 内のこの行では、isnewstatus が 0 に設定されているため、MERGE ステートメントは merge_example_mult_target の status 値を更新しません。
merge_example_mult_source に行が存在し、merge_example_mult_target に一致する行がないため、id を 4 に設定して行を挿入します。

MERGE INTO merge_example_mult_target
  USING merge_example_mult_source
  ON merge_example_mult_target.id = merge_example_mult_source.id
  WHEN MATCHED AND merge_example_mult_source.marked = 'Y'
    THEN DELETE
  WHEN MATCHED AND merge_example_mult_source.isnewstatus = 1
    THEN UPDATE SET val = merge_example_mult_source.newval, status = merge_example_mult_source.newstatus
  WHEN MATCHED
    THEN UPDATE SET val = merge_example_mult_source.newval
  WHEN NOT MATCHED
    THEN INSERT (id, val, status) VALUES (
      merge_example_mult_source.id,
      merge_example_mult_source.newval,
      merge_example_mult_source.newstatus);

Copy

+-------------------------+------------------------+------------------------+
| number of rows inserted | number of rows updated | number of rows deleted |
|-------------------------+------------------------+------------------------|
|                       1 |                      2 |                      1 |
+-------------------------+------------------------+------------------------+

マージ結果を見るには、merge_example_mult_target テーブルの値を表示します。

SELECT * FROM merge_example_mult_target ORDER BY id;

Copy

+----+-----+------------+
| ID | VAL | STATUS     |
|----+-----+------------|
|  2 |  50 | Beta       |
|  3 |  60 | Production |
|  4 |  40 | Production |
+----+-----+------------+

ALL BY NAME を使用してマージを実行します¶

次の例は、ソーステーブルの値を使用してターゲットテーブルの値を挿入および更新するマージを実行します。この例では、WHEN MATCHED ... THEN ALL BY NAME および WHEN NOT MATCHED ... THEN ALL BY NAME サブ句を使用して、マージがすべての列に適用されることを指定しています。

列の数と列名が同じで、2つの列の順序が異なる2つのテーブルを作成します。

CREATE OR REPLACE TABLE merge_example_target_all (
  id INTEGER,
  x INTEGER,
  y VARCHAR);

CREATE OR REPLACE TABLE merge_example_source_all (
  id INTEGER,
  y VARCHAR,
  x INTEGER);

Copy

テーブルをロードします。

INSERT INTO merge_example_target_all (id, x, y) VALUES
  (1, 10, 'Skiing'),
  (2, 20, 'Snowboarding');

INSERT INTO merge_example_source_all (id, y, x) VALUES
  (1, 'Skiing', 10),
  (2, 'Snowboarding', 25),
  (3, 'Skating', 30);

Copy

テーブルの値を表示します。

SELECT * FROM merge_example_target_all;

Copy

+----+----+--------------+
| ID |  X | Y            |
|----+----+--------------|
|  1 | 10 | Skiing       |
|  2 | 20 | Snowboarding |
+----+----+--------------+

SELECT * FROM merge_example_source_all;

Copy

+----+--------------+----+
| ID | Y            |  X |
|----+--------------+----|
|  1 | Skiing       | 10 |
|  2 | Snowboarding | 25 |
|  3 | Skating      | 30 |
+----+--------------+----+

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

MERGE INTO merge_example_target_all
  USING merge_example_source_all
  ON merge_example_target_all.id = merge_example_source_all.id
  WHEN MATCHED THEN
    UPDATE ALL BY NAME
  WHEN NOT MATCHED THEN
    INSERT ALL BY NAME;

Copy

+-------------------------+------------------------+
| number of rows inserted | number of rows updated |
|-------------------------+------------------------|
|                       1 |                      2 |
+-------------------------+------------------------+

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

SELECT *
  FROM merge_example_target_all
  ORDER BY id;

Copy

+----+----+--------------+
| ID |  X | Y            |
|----+----+--------------|
|  1 | 10 | Skiing       |
|  2 | 25 | Snowboarding |
|  3 | 30 | Skating      |
+----+----+--------------+

重複ソースとマージを実行する¶

ソースに重複する値があり、ターゲットに一致する値がないマージを実行します。ソース記録のすべてのコピーがターゲットに挿入されます。詳細については、INSERT の決定的な結果を参照してください。

両方のテーブルを切り捨て、重複を含む新しい行をソーステーブルにロードします。

TRUNCATE table merge_example_target;

TRUNCATE table merge_example_source;

INSERT INTO merge_example_source (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');

Copy

merge_example_target には値がありません。merge_example_source テーブルの値を表示します。

SELECT * FROM merge_example_source;

Copy

+----+--------------------------------------------------------------+
| ID | DESCRIPTION                                                  |
|----+--------------------------------------------------------------|
| 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 merge_example_target
  USING merge_example_source
  ON merge_example_target.id = merge_example_source.id
  WHEN MATCHED THEN
    UPDATE SET merge_example_target.description = merge_example_source.description
  WHEN NOT MATCHED THEN
    INSERT (id, description) VALUES
      (merge_example_source.id, merge_example_source.description);

Copy

+-------------------------+------------------------+
| number of rows inserted | number of rows updated |
|-------------------------+------------------------|
|                       2 |                      0 |
+-------------------------+------------------------+

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

SELECT * FROM merge_example_target;

Copy

+----+--------------------------------------------------------------+
| ID | DESCRIPTION                                                  |
|----+--------------------------------------------------------------|
| 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 |
+----+--------------------------------------------------------------+

決定論的結果と非決定論的結果とのマージを実行する¶

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

2つのテーブルを作成し、ロードします。

CREATE OR REPLACE TABLE merge_example_target_orig (k NUMBER, v NUMBER);

INSERT INTO merge_example_target_orig VALUES (0, 10);

CREATE OR REPLACE TABLE merge_example_src (k NUMBER, v NUMBER);

INSERT INTO merge_example_src VALUES (0, 11), (0, 12), (0, 13);

Copy

次の例でマージを実行すると、複数の更新が互いに競合します。ERROR_ON_NONDETERMINISTIC_MERGE セッションパラメーターが true に設定されている場合、MERGE ステートメントはエラーを返します。そうでない場合、MERGE ステートメントは、重複行（定義されていない行）のいずれかの値（例えば、11 、12 、13）で merge_example_target_clone.v を更新します。

CREATE OR REPLACE TABLE merge_example_target_clone
  CLONE merge_example_target_orig;

MERGE INTO  merge_example_target_clone
  USING merge_example_src
  ON merge_example_target_clone.k = merge_example_src.k
  WHEN MATCHED THEN UPDATE SET merge_example_target_clone.v = merge_example_src.v;

Copy

更新と削除は互いに競合します。ERROR_ON_NONDETERMINISTIC_MERGE セッションパラメーターが true に設定されている場合、MERGE ステートメントはエラーを返します。そうでない場合、MERGE ステートメントは、行を削除するか、重複行（定義されていない行）のいずれかの値（例えば、12 や``13``）で merge_example_target_clone.v を更新します。

CREATE OR REPLACE TABLE merge_example_target_clone
  CLONE merge_example_target_orig;

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

Copy

複数の削除が競合することはありません。どの句にも一致しない結合値は、削除を妨げません（merge_example_src.v = 13）MERGE ステートメントは成功し、対象行は削除されます。

CREATE OR REPLACE TABLE target CLONE merge_example_target_orig;

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

Copy

どの句にも一致しない結合値は、更新を妨げません（merge_example_src.v = 12, 13）MERGE ステートメントは成功し、ターゲット行は target.v = 11 に設定されます。

CREATE OR REPLACE TABLE merge_example_target_clone CLONE target_orig;

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

Copy

各ターゲット行がソース内の1つの行に対して結合されるように、ソース句で GROUP BY を使用します。

CREATE OR REPLACE TABLE merge_example_target_clone CLONE merge_example_target_orig;

MERGE INTO merge_example_target_clone
  USING (SELECT k, MAX(v) AS v FROM merge_example_src GROUP BY k) AS b
  ON merge_example_target_clone.k = b.k
  WHEN MATCHED THEN UPDATE SET merge_example_target_clone.v = b.v
  WHEN NOT MATCHED THEN INSERT (k, v) VALUES (b.k, b.v);

Copy

DATE の値に基づいてマージを実行する¶

次の例では、 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;

Copy

MERGE¶

構文¶

パラメーター¶

matchedClause （更新または削除用）¶

notMatchedClause （挿入用）¶

使用上の注意¶

結合動作を複製する¶

UPDATE と DELETE の非決定的結果¶

UPDATE と DELETE の決定的な結果¶

INSERT の決定的な結果¶

例¶

値を更新する基本的なマージを実行する¶

複数の操作による基本的なマージを実行する¶

ALL BY NAME を使用してマージを実行します¶

重複ソースとマージを実行する¶

決定論的結果と非決定論的結果とのマージを実行する¶

DATE の値に基づいてマージを実行する¶

`matchedClause` （更新または削除用）¶

`notMatchedClause` （挿入用）¶