MERGE¶

根据第二个表或子查询中的值，在表中插入、更新和删除值。如果第二个表是变更日志，包含目标表中的新行（要插入）、修改的行（要更新）或标记的行（要删除），则合并操作非常有用。

该命令支持用于处理以下情况的语义：

匹配的值（用于更新和删除）。
不匹配的值（用于插入）。

另请参阅：: DELETE、 UPDATE

语法¶

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

其中：

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

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

参数¶

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 [ , ... ] )

（可选）指定目标表中一个或多个列，用以插入来自源表的数据值。

默认值：无值（目标表中的所有列都会插入）

VALUES ( expr [ , ... ] )

指定插入的列值的相应表达式（必须引用源关系）。

使用说明¶

单个 MERGE 语句可以包含多个匹配和不匹配的子句（即 WHEN MATCHED ... 和 WHEN NOT MATCHED ...）。
任何省略 AND 子句（默认行为）的匹配或不匹配的子句都必须是语句中其子句类型的 :emph:` 最后一个 ` （例如，WHEN MATCHED ... 子句后面不能跟着 WHEN MATCHED AND ... 子句）。这样会导致无法访问的情况，并返回错误。

重复联接行为¶

当源表中的多行与目标表中的单行匹配时，结果可以是确定性的，也可以是不确定性的。本节介绍这些用例的 MERGE 行为。

UPDATE 和 DELETE 的非确定性结果¶

当合并将目标表中的一行与源中的多行联接时，以下联接条件将产生不确定的结果（即系统无法确定用于更新或删除目标行的源值）：

选择目标行，以使用多个值进行更新（例如 WHEN MATCHED ... THEN UPDATE）。
选择目标行，以更新并删除（例如 WHEN MATCHED ... THEN UPDATE、 WHEN MATCHED ... THEN DELETE）。

在此情况下，合并的结果取决于为 ERROR_ON_NONDETERMINISTIC_MERGE 会话参数指定的值：

如果为 TRUE（默认值），则合并将返回错误。
如果为 FALSE，则从重复项中选择一行，以执行更新或删除；不定义所选行。

UPDATE 和 DELETE 的确定性结果¶

确定性合并始终完成，没有错误。如果合并满足每个目标行的以下 至少一个 条件，则合并是确定的：

一个或多个源行满足 WHEN MATCHED ... THEN DELETE 子句，而其他源行不满足任何 WHEN MATCHED 子句
只有一个源行满足 WHEN MATCHED ... THEN UPDATE 子句，没有其他源行满足任何 WHEN MATCHED 子句。

这使得 MERGE 在语义上等同于 UPDATE 和 DELETE 命令。

备注

根据 ON 条件，数据源中的多行（即源表或子查询）与目标表匹配时，为避免出现错误，在源子句中使用 GROUP BY，以确保每个目标行（最多）与源中的一行联接。

在以下示例中，假设 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);

INSERT 的确定性结果¶

确定性合并始终完成，没有错误。

如果 MERGE 语句包含 WHEN NOT MATCHED ... THEN INSERT 子句，目标表中没有匹配的行，并且源表包含重复值，则目标表会针对源表中的 每一个 重复项插入一份对应的行。有关示例，请参阅执行源表存在重复项的 MERGE 操作。

示例¶

以下示例使用 MERGE 命令：

执行更新值的基本合并
执行包含多个操作的基本合并
使用 ALL BY NAME 执行合并
执行源表存在重复项的 MERGE 操作
对确定性结果和非确定性结果执行合并
基于 DATE 值执行合并

执行更新值的基本合并¶

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

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

显示表中的值：

SELECT * FROM merge_example_target;

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

SELECT * FROM merge_example_source;

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

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

在目标表中显示新值（源表保持不变）：

SELECT * FROM merge_example_target;

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

SELECT * FROM merge_example_source;

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

执行包含多个操作的基本合并¶

使用混合操作（INSERT、UPDATE 和 DELETE）执行基本合并。

创建并加载两个表：

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

显示表中的值：

SELECT * FROM merge_example_mult_target;

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

SELECT * FROM merge_example_mult_source;

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

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

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

加载表：

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

显示表中的值：

SELECT * FROM merge_example_target_all;

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

SELECT * FROM merge_example_source_all;

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

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

在目标表中显示新值：

SELECT *
  FROM merge_example_target_all
  ORDER BY id;

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

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

执行合并，其中源具有重复值而目标没有匹配值。源记录的所有副本都插入目标中。有关更多信息，请参阅 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');

merge_example_target 没有值。显示 merge_example_source 表中的值：

SELECT * FROM merge_example_source;

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

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

在目标表中显示新值：

SELECT * FROM merge_example_target;

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

对确定性结果和非确定性结果执行合并¶

通过使用生成非确定性和确定性结果的联接，合并记录：

创建并加载两个表：

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

在以下示例中执行合并时，多个更新会相互冲突。如果 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;

更新和删除相互冲突。如果 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;

多次删除不会相互冲突。与任何子句都不匹配的联接值不会阻止删除 (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;

与任何子句都不匹配的联接值不会阻止更新 (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;

使用源子句中的 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);

基于 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;

MERGE¶

语法¶

参数¶

matchedClause （用于更新或删除）¶

notMatchedClause （用于插入）¶

使用说明¶

重复联接行为¶

UPDATE 和 DELETE 的非确定性结果¶

UPDATE 和 DELETE 的确定性结果¶

INSERT 的确定性结果¶

示例¶

执行更新值的基本合并¶

执行包含多个操作的基本合并¶

使用 ALL BY NAME 执行合并¶

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

对确定性结果和非确定性结果执行合并¶

基于 DATE 值执行合并¶

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

`notMatchedClause` （用于插入）¶