MERGE¶

Inserts, updates, and deletes values in a table that are based on values in a second table or a subquery. Merging can be useful if the second table is a change log that contains new rows (to be inserted), modified rows (to be updated), or marked rows (to be deleted) in the target table.

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

一致する値（更新および削除用）。
Values that don't match (for inserts).

こちらもご参照ください。: 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 ... ]

Updates the specified column in the target table by using the corresponding expression for the new column value (can refer to both the target and source relations).

In a single SET subclause, you can specify multiple columns to update.

DELETE

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

`notMatchedClause` （挿入用）¶

WHEN NOT MATCHED ... AND case_predicate

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

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

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

Specifies the action to perform when the values don't match.

ALL BY NAME

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

( col_name [ , ... ] )

Optionally specifies one or more columns in the target table to be inserted with values from the source.

Default: No value (all columns in the target table are inserted)

VALUES ( expr [ , ... ] )

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

使用上の注意¶

A single MERGE statement can include multiple matching and not-matching clauses (that is, WHEN MATCHED ... and WHEN NOT MATCHED ...).
Any matching or not-matching clause that omits the AND subclause (default behavior) must be the last of its clause type in the statement (for example, a WHEN MATCHED ... clause can't be followed by a WHEN MATCHED AND ... clause). Doing so results in an unreachable case, which returns an error.

結合動作を複製する¶

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

UPDATE と DELETE の非決定的結果¶

When a merge joins a row in the target table against multiple rows in the source, the following join conditions produce nondeterministic results (that is, the system is unable to determine the source value to use to update or delete the target row):

A target row is selected to be updated with multiple values (for example, WHEN MATCHED ... THEN UPDATE).
A target row is selected to be both updated and deleted (for example, WHEN MATCHED ... THEN UPDATE , WHEN MATCHED ... THEN DELETE).

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

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

UPDATE と DELETE の決定的な結果¶

Deterministic merges always complete without error. A merge is deterministic if it meets at least one of the following conditions for each target row:

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

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

注釈

To avoid errors when multiple rows in the data source (that is, the source table or subquery) match the target table based on the ON condition, use GROUP BY in the source clause to ensure that each target row joins against one row (at most) in the source.

In the following example, assume src includes multiple rows with the same k value. It's ambiguous which values (v) will be used to update rows in the target row with the same value of k. By using the MAX function and GROUP BY, the query clarifies exactly which value of v from src is used:

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 の決定的な結果¶

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

If the MERGE statement contains a WHEN NOT MATCHED ... THEN INSERT clause, and if there are no matching rows in the target, and if the source contains duplicate values, then the target gets one copy of the row for each copy in the source. For an example, see 重複ソースとマージを実行する.

例¶

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

Perform a basic merge that updates values
Perform a basic merge with multiple operations
ALL BY NAME を使用してマージを実行します
重複ソースとマージを実行する
Perform a merge with deterministic and nondeterministic results
DATE の値に基づいてマージを実行する

Perform a basic merge that updates values¶

以下の例では、ソーステーブルの値を使用してターゲットテーブルの値を更新する基本的なマージを実行します。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

Display the values in the tables:

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

Run the MERGE statement:

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

Display the new values in the target table (the source table is unchanged):

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

Perform a basic merge with multiple operations¶

Perform a basic merge with a mix of operations (INSERT, UPDATE, and DELETE).

Create and load two tables:

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

Display the values in the tables:

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

Load the tables:

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

Display the values in the tables:

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

Run the MERGE statement:

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

Display the new values in the target table:

SELECT *
  FROM merge_example_target_all
  ORDER BY id;

Copy

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

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

Perform a merge in which the source has duplicate values and the target has no matching values. All copies of the source record are inserted into the target. For more information, see INSERT の決定的な結果.

Truncate both tables and load new rows into the source table that include duplicates:

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

Run the MERGE statement:

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

Display the new values in the target table:

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

Perform a merge with deterministic and nondeterministic results¶

Merge records by using joins that produce nondeterministic and deterministic results.

Create and load two tables:

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 の決定的な結果¶

例¶

Perform a basic merge that updates values¶

Perform a basic merge with multiple operations¶

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

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

Perform a merge with deterministic and nondeterministic results¶

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

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

`notMatchedClause` （挿入用）¶