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.

重复联接行为¶

当源表中的多行与目标表中的单行匹配时，结果可以是确定性的，也可以是不确定性的。本节介绍这些用例的 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，则从重复项中选择一行，以执行更新或删除；不定义所选行。

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:

一个或多个源行满足 WHEN MATCHED ... THEN DELETE 子句，而其他源行不满足任何 WHEN MATCHED 子句
只有一个源行满足 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 操作.

示例¶

以下示例使用 MERGE 命令：

Perform a basic merge that updates values
Perform a basic merge with multiple operations
使用 ALL BY NAME 执行合并
执行源表存在重复项的 MERGE 操作
Perform a merge with deterministic and nondeterministic results
基于 DATE 值执行合并

Perform a basic merge that updates values¶

以下示例执行基本合并，使用源表中的值更新目标表中的值。创建并加载两个表：

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 表执行以下操作：

删除 id 设为 1 的行，因为在 merge_example_mult_source 中，具有相同 id 的行的 marked 列值为 Y。
将 id 为 2 的行中的 val 与 status 值更新为 merge_example_mult_source 中 id 相同的行中的值，因为在 merge_example_mult_source 中，该行的 isnewstatus 已设置为 1。
将目标表中 id 设为 3 的行的 val 值更新为 merge_example_mult_source 中 id 相同的行的值。由于在 merge_example_mult_source 中该行的 isnewstatus 已设置为 0，因此 MERGE 语句不更新 merge_example_mult_target 中的 status 值。
插入 id 设为 4 的行，因为该行存在于 merge_example_mult_source 中，并且 merge_example_mult_target 中无匹配行。

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 分子句来指定合并应用于所有列。

创建两个具有相同列数和相同列名称的表，但其中两列的顺序不同：

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

执行源表存在重复项的 MERGE 操作¶

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 语句会将 merge_example_target_clone.v 更新为重复行中的某一个值（例如 11、12 或 13），但具体使用哪一行未定义：

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 语句会删除行或将 merge_example_target_clone.v 更新为重复行中的某一个值（例如 12 或 13），但具体使用哪一行未定义：

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

使用源子句中的 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 执行合并¶

执行源表存在重复项的 MERGE 操作¶

Perform a merge with deterministic and nondeterministic results¶

基于 DATE 值执行合并¶

`matchedClause` （用于更新或删除）¶

`notMatchedClause` （用于插入）¶