MERGE

MERGE — 有条件地插入、更新或删除表中的行

语法

[ WITH with_query [, ...] ]
MERGE INTO [ ONLY ] target_table_name [ * ] [ [ AS ] target_alias ]
USING data_source ON join_condition
when_clause [...]

where data_source is:

{ [ ONLY ] source_table_name [ * ] | ( source_query ) } [ [ AS ] source_alias ]

and when_clause is:

{ WHEN MATCHED [ AND condition ] THEN { merge_update | merge_delete | DO NOTHING } |
  WHEN NOT MATCHED [ AND condition ] THEN { merge_insert | DO NOTHING } }

and merge_insert is:

INSERT [( column_name [, ...] )]
[ OVERRIDING { SYSTEM | USER } VALUE ]
{ VALUES ( { expression | DEFAULT } [, ...] ) | DEFAULT VALUES }

and merge_update is:

UPDATE SET { column_name = { expression | DEFAULT } |
             ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]

and merge_delete is:

DELETE

说明

MERGE执行操作来修改*target_table_name中的行，使用data_source*。MERGE提供了一个SQL语句，该语句可以有条件地INSERT、UPDATE或DELETE行，而此任务通常需要多个过程语言语句。

首先，MERGE命令从*data_source到target_table_name*执行联接，生成零个或多个候选更改行。对于每个候选更改行，MATCHED或NOT MATCHED状态只设置一次，之后WHEN子句按指定的顺序进行评估。对于每个候选更改行，第一个评估为 true 的子句将被执行。对于任何候选更改行，最多只执行一个WHEN子句。

MERGE操作与同名常规UPDATE、INSERT或DELETE命令具有相同的效果。这些命令的语法不同，特别是没有WHERE子句，并且没有指定表名。所有操作都引用*target_table_name*，尽管可以使用触发器对其他表进行修改。

当指定DO NOTHING时，将跳过源行。由于按指定顺序评估操作，DO NOTHING可用于在进行更细粒度的处理之前跳过无意义的源行。

没有单独的MERGE权限。如果您指定更新操作，则必须对SET子句中引用的*target_table_name的列具有UPDATE权限。如果您指定插入操作，则必须对target_table_name具有INSERT权限。如果您指定删除操作，则必须对target_table_name具有DELETE权限。权限在语句开始时测试一次，并且无论是否执行特定WHEN子句而进行检查。您将需要对任何condition或expression中引用的data_source和target_table_name*的任何列具有SELECT权限。

如果*target_table_name*是物化视图、外部表，或已对其定义任何规则，则不支持MERGE。

参数

target_table_name

要合并到的目标表的名称（可选的模式限定）。如果在表名前指定 ONLY，则仅在命名的表中更新或删除匹配的行。如果未指定 ONLY，则在从命名表继承的任何表中也会更新或删除匹配的行。或者，可以在表名后指定 * 以明确指示包括后代表。ONLY 关键字和 * 选项不影响插入操作，后者始终仅插入到命名的表中。

target_alias

目标表的替代名称。提供别名时，它会完全隐藏表的实际名称。例如，给定 MERGE INTO foo AS f，MERGE 语句的其余部分必须将此表称为 f，而不是 foo。

source_table_name

源表、视图或转换表的名称（可选的模式限定）。如果在表名前指定 ONLY，则仅从命名的表中包括匹配的行。如果未指定 ONLY，则在从命名表继承的任何表中也会包括匹配的行。或者，可以在表名后指定 * 以明确指示包括后代表。

source_query

提供要合并到target_table_name 中的行的一个查询（SELECT 语句或 VALUES 语句）。有关语法的说明，请参阅 SELECT 语句或 VALUES 语句。

source_alias

数据源的替代名称。提供别名时，它会完全隐藏表的实际名称或已发出查询这一事实。

join_condition

join_condition 是一个表达，其结果为 boolean 类型的数值（类似于 WHERE 子句），它指定 data_source 中哪些行与 target_table_name 中的行匹配。

警告

仅当尝试匹配 data_source 行的 target_table_name 中的列时，才应在 join_condition 中显示这些列。仅引用 target_table_name 列的 join_condition 子表达式会影响执行的操作，通常会以令人惊讶的方式影响。

when_clause

至少需要一个 WHEN 子句。

如果 WHEN 子句指定 WHEN MATCHED，并且候选更改行与 target_table_name 中的行匹配，则在 condition 不存在或其评估结果为 true 时，将执行 WHEN 子句。

相反，如果 WHEN 子句指定 WHEN NOT MATCHED，并且候选更改行与 target_table_name 中的行不匹配，则在 condition 不存在或其评估结果为 true 时，将执行 WHEN 子句。

condition

返回 boolean 类型的数值的表达式。如果 WHEN 子句的此表达式返回 true，则将针对该行执行该子句的操作。

在 WHEN MATCHED 子句中的条件可以引用源关系和目标关系中的列。在 WHEN NOT MATCHED 子句中的条件只能引用源关系中的列，因为根据定义，没有匹配的目标行。只能访问目标表的系统属性。

merge_insert

将一行插入目标表的 INSERT 操作规范。目标列名称可以按任何顺序列出。如果根本没有给出列名称列表，则默认值为表的所有列，按其声明的顺序排列。

显式或隐式列列表中不存在的每个列都将填充默认值，即其声明的默认值或（如果没有）为 null。

如果 target_table_name 是分区表，则每行都会路由到适当的分区并插入其中。如果 target_table_name 是分区，则如果任何输入行违反分区约束，将发生错误。

列名称不能指定多次。 INSERT 操作不能包含子选择。

只能指定一个 VALUES 子句。 VALUES 子句只能引用源关系中的列，因为根据定义，没有匹配的目标行。

merge_update

更新 target_table_name 的当前行的 UPDATE 操作规范。列名称不能指定多次。

不允许表名称或 WHERE 子句。

merge_delete

指定 DELETE 操作，该操作删除 target_table_name 的当前行。不要包含表名称或任何其他子句，就像通常使用 DELETE 命令一样。

column_name

target_table_name 中列的名称。如果需要，列名称可以用子字段名称或数组下标限定。（仅插入复合列的某些字段会使其他字段保持 null。）不要在目标列的规范中包含表的名称。

覆盖系统值

如果没有此子句，则为标识列定义为 GENERATED ALWAYS 指定显式值（DEFAULT 除外）将出错。此子句将覆盖该限制。

覆盖用户值

如果指定此子句，则将忽略为定义为 GENERATED BY DEFAULT 的标识列提供的任何值，并应用默认序列生成的值。

默认值

所有列都将填充其默认值。（此形式中不允许 OVERRIDING 子句。）

表达式

要分配给列的表达式。如果在 WHEN MATCHED 子句中使用，则该表达式可以使用目标表中原始行中的值和 data_source 行中的值。如果在 WHEN NOT MATCHED 子句中使用，则该表达式可以使用 data_source 中的值。

默认值

将列设置为其默认值（如果未为其分配特定的默认表达式，则为 NULL）。

with_query

使用 WITH 子句，您可以在 MERGE 查询中按名称引用一个或多个子查询。有关详细信息，请参见 第 7.8 节 和 SELECT。

输出

成功完成后，MERGE命令将返回以下形式的命令标记

MERGE total_count

*total_count是更改的总行数（无论插入、更新还是删除）。如果total_count*为 0，则未以任何方式更改任何行。

备注

在执行MERGE期间，将执行以下步骤。

1. 对所有指定的动作执行任何 BEFORE STATEMENT 触发器，无论其 WHEN 子句是否匹配。

2. 从源表执行到目标表的联接。将对结果查询进行正常优化，并将生成一组候选更改行。对于每个候选更改行，

   1. 评估每行是 MATCHED 还是 NOT MATCHED。

   2. 按指定顺序测试每个 WHEN 条件，直到一个返回 true。

   3. 当条件返回 true 时，执行以下操作

      1. 执行针对动作事件类型触发的任何 BEFORE ROW 触发器。

      2. 执行指定的操作，调用目标表上的任何检查约束。

      3. 执行针对动作事件类型触发的任何 AFTER ROW 触发器。

3. 对指定的动作执行任何 AFTER STATEMENT 触发器，无论它们是否实际发生。这类似于修改零行的 UPDATE 语句的行为。

总之，每当我们指定此类动作时，将触发事件类型的语句触发器（例如，INSERT）。相比之下，行级触发器仅针对正在执行的特定事件类型触发。因此，MERGE命令可能会同时触发UPDATE和INSERT的语句触发器，即使仅触发了UPDATE行触发器。

您应确保联接为每个目标行生成至多一个候选更改行。换句话说，目标行不应联接到多个数据源行。如果联接到多个数据源行，那么仅使用其中一个候选更改行来修改目标行；稍后尝试修改该行将导致错误。如果行触发器对目标表进行更改，并且随后MERGE也修改了这些经过修改的行，则也可能发生这种情况。如果重复的操作是INSERT，这将导致唯一性冲突，而重复的UPDATE或DELETE将导致基数冲突；后者行为是SQL标准所要求的。这与PostgreSQL在UPDATE和DELETE语句中联接的历史行为不同，在这些语句中，第二次及以后尝试修改同一行都会被简单忽略。

如果WHEN子句省略AND子句，它将成为该类型的最终可达子句（MATCHED或NOT MATCHED）。如果指定了该类型的后续WHEN子句，则它将被证明不可达，并且会引发错误。如果没有指定任何类型的最终可达子句，则候选更改行可能不会执行任何操作。

默认情况下，从数据源生成行的顺序是不确定的。如果需要，可以使用*source_query*指定一致的排序，这可能需要避免并发事务之间的死锁。

MERGE没有RETURNING子句。INSERT、UPDATE和DELETE操作不能包含RETURNING或WITH子句。

当MERGE与修改目标表的其他命令并发运行时，将应用通常的事务隔离规则；有关每个隔离级别的行为说明，请参见第 13.2 节。您还可以考虑使用INSERT ... ON CONFLICT作为替代语句，该语句提供在发生并发INSERT时运行UPDATE的功能。这两种语句类型之间存在多种差异和限制，它们不可互换。

示例

根据新的recent_transactions对customer_accounts执行维护。

MERGE INTO customer_account ca
USING recent_transactions t
ON t.customer_id = ca.customer_id
WHEN MATCHED THEN
  UPDATE SET balance = balance + transaction_value
WHEN NOT MATCHED THEN
  INSERT (customer_id, balance)
  VALUES (t.customer_id, t.transaction_value);

请注意，这与以下语句完全等效，因为MATCHED结果在执行期间不会更改。

MERGE INTO customer_account ca
USING (SELECT customer_id, transaction_value FROM recent_transactions) AS t
ON t.customer_id = ca.customer_id
WHEN MATCHED THEN
  UPDATE SET balance = balance + transaction_value
WHEN NOT MATCHED THEN
  INSERT (customer_id, balance)
  VALUES (t.customer_id, t.transaction_value);

尝试插入新库存项目以及库存数量。如果该项目已存在，则更新现有项目的库存数量。不允许库存为零的条目。

MERGE INTO wines w
USING wine_stock_changes s
ON s.winename = w.winename
WHEN NOT MATCHED AND s.stock_delta > 0 THEN
  INSERT VALUES(s.winename, s.stock_delta)
WHEN MATCHED AND w.stock + s.stock_delta > 0 THEN
  UPDATE SET stock = w.stock + s.stock_delta
WHEN MATCHED THEN
  DELETE;

例如，wine_stock_changes表可能是最近加载到数据库中的临时表。

兼容性

此命令符合SQL标准。

WITH子句和DO NOTHING操作是对SQL标准的扩展。