MERGE¶

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

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

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

另请参阅：: DELETE、UPDATE

语法¶

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

Copy

其中：

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

Copy

notMatchedClause ::=
   WHEN NOT MATCHED [ AND <case_predicate> ] THEN INSERT [ ( <col_name> [ , ... ] ) ] VALUES ( <expr> [ , ... ] )

Copy

参数¶

target_table: 指定要合并的表。
source: 指定要与目标表联接的表或子查询。
join_expr: 指定要联接目标表和源的表达式。

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

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

指定在值匹配时要执行的操作。

AND case_predicate

（可选）指定一个表达式，当为 True 时，该表达式将导致执行匹配大小写。

默认：无值（始终执行匹配大小写）

SET col_name = expr [ ... ]

指定目标表中要更新或插入的列，以及新列值的相应表达式（可以同时引用目标关系和源关系）。

在单个 SET 分子句中，您可以指定要更新/删除的多个列。

`notMatchedClause` （用于插入）¶

WHEN NOT MATCHED ... THEN INSERT

指定在值不匹配时要执行的操作。

AND case_predicate

（可选）指定一个表达式，当为 True 时，该表达式将导致执行不匹配的大小写。

默认：无值（始终执行不匹配的大小写）

( col_name [ , ... ] )

（可选）指定目标表中要更新或插入的一个或多个列。

默认：无值（更新或插入的目标表中的所有列）

VALUES ( expr [ , ... ] )

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

使用说明¶

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

重复联接行为¶

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 子句

OR
只有一个源行满足 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);

Copy

INSERT 的确定性结果¶

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

如果 MERGE 包含 WHEN NOT MATCHED ... THEN INSERT 子句，目标中没有匹配的行，并且源包含重复值，则目标将获得源中针对 each 复制的行的副本。（下面包括一个示例。）

示例¶

执行简单的合并：

创建并加载表：

CREATE TABLE target_table (ID INTEGER, description VARCHAR);

CREATE TABLE source_table (ID INTEGER, description VARCHAR);

Copy

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

Copy

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

Copy

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

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

Copy

使用混合操作（删除、更新、插入）执行基本合并：

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

Copy

执行合并，其中源具有重复值而目标没有匹配值。请注意，源记录的所有副本都插入目标中：

截断两个表并将新行加载到源表中。请注意，这些行包含重复项。

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

Copy

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

Copy

在目标表中显示新值：

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

Copy

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

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

Copy

在以下示例中，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 的确定性结果¶

示例¶

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

`notMatchedClause` （用于插入）¶