结构化查询语言/MERGE

提示：小心并禁用AUTOCOMMIT.

在很多情况下，应用程序希望将行存储到数据库中，而不必知道这些行是否之前已经存在于数据库中。如果行存在，则必须使用 UPDATE 命令，否则使用 INSERT 命令。为此，通常使用以下结构

-- pseudocode
IF (SELECT COUNT(*) = 0 ...) THEN
  INSERT ...
ELSE
  UPDATE ...
;

这种情况在很多方面都不理想

应用程序和 DBMS 之间存在两次往返，要么是 SELECT + INSERT，要么是 SELECT + UPDATE。
应用程序必须一次传输一行。无法进行“批量存储”，因为决定 INSERT 和 UPDATE 之间的条件的评估可能会导致行与行之间结果不同。
语法分散在三个 SQL 语句中。这容易出错。

为了克服这些缺点，SQL 标准定义了 MERGE 命令，该命令将上述完整代码包含在一个语句中。MERGE 根据目标表中单个行的存在性执行 INSERT 或 UPDATE 操作。

-- Define target, source, match criterion, INSERT and UPDATE within one single command
MERGE INTO  <target_table>      <target_table_alias>  -- denote the target table
      USING <syntaxhighlight_table>      <syntaxhighlight_table_alias>  -- denote the source table
      ON    (<match_criterion>)                       -- define the 'match criterion' which compares the source and 
                                                      -- target rows with the same syntax as in any WHERE clause  
  WHEN MATCHED THEN
UPDATE SET column1 = value1 [, column2 = value2 ...]  -- a variant of the regular UPDATE command
  WHEN NOT MATCHED THEN
INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...]) -- a variant of the regular INSERT command
;

描述

目标表以 MERGE INTO 关键字命名，源表以 USING 关键字命名。

目标行和源行之间的比较（这是决定 INSERT 和 UPDATE 操作所必需的）在 ON 关键字之后使用与 WHERE 子句语法相同的语法指定。如果此比较匹配，则执行 UPDATE 操作，否则执行 INSERT 操作。在简单情况下，比较将比较主键或外键列。但也可以对任何列使用非常复杂的条件。

在“匹配”情况下，会执行 UPDATE 的变体。它与常规 UPDATE 命令的不同之处在于它没有表名（表名已在 MERGE INTO 之后表示）并且没有 WHERE 子句（它使用 ON 关键字之后的匹配条件）。

在“不匹配”情况下，会执行 INSERT 的变体。与之前相同的原因，目标表不会在 INSERT 中命名。

示例

创建一个表“hobby_shadow”来存储一些“hobby”行。随后的 MERGE 命令将根据相关行的存在性执行 INSERT 或 UPDATE 操作。

-- store every second row in a new table 'hobby_shadow'
CREATE TABLE hobby_shadow AS SELECT * FROM hobby WHERE MOD(id, 2) = 0;
SELECT * FROM hobby_shadow;

-- INSERT / UPDATE depending on the column 'id'.
MERGE INTO  hobby_shadow                   t   -- the target
      USING (SELECT id, hobbyname, remark
             FROM   hobby)                 s   -- the source
      ON    (t.id = s.id)                      -- the 'match criterion'
  WHEN MATCHED THEN
UPDATE SET remark = concat(s.remark, ' Merge / Update')
  WHEN NOT MATCHED THEN
INSERT (id, hobbyname, remark) VALUES (s.id, s.hobbyname, concat(s.remark, ' Merge / Insert'))
;
COMMIT;

-- Check the result
SELECT * FROM hobby_shadow;

MERGE 命令处理所有行，但应用程序和 DBMS 之间只有一次往返。一些行由 MERGE 的 INSERT 部分处理，另一些行由其 UPDATE 部分处理。可以通过“remark”列的最后一部分观察这种区别。

用例

MERGE 命令的典型用例是 ETL 进程。通常，这些进程必须在一段时间内针对分组条件（例如：产品线）聚合一些值。每个产品线和期间的第一次访问必须插入具有给定值的新行，后续访问必须通过增加值来更新这些行。

扩展

SQL 标准在 MERGE 命令中定义了一些其他功能。

WHEN 子句
WHEN MATCHED 和 WHEN NOT MATCHED 子句可以使用可选的查询表达式进行扩展，例如 AND (place_of_birth = 'Dallas')。因此，可以使用一系列 WHEN MATCHED/WHEN NOT MATCHED 子句。

... 
  WHEN MATCHED AND (t.hobby_name IN ('Fishing', 'Underwater Diving')) THEN
UPDATE SET remark = concat('Water sports: ', t.remark)
  WHEN MATCHED AND (t.hobby_name IN ('Astronomy', 'Microscopy', 'Literature')) THEN
UPDATE SET remark = concat('Semi-professional leisure activity: ', t.remark)
  WHEN MATCHED THEN
UPDATE SET remark = concat('Leisure activity: ', t.remark)
...
-- The same is possible with WHEN NOT MATCHED in combination with INSERT

DELETE
在 WHEN MATCHED 子句中，可以使用 DELETE 命令来代替 UPDATE 操作来删除匹配的行。此功能可以与之前介绍的可选查询表达式的扩展相结合。在 SQL 标准中，DELETE 命令不适用于 WHEN NOT MATCHED 子句。

-- Update 'Fishing' and 'Underwater Diving'. Delete all others which have a match between source and target.
...
  WHEN MATCHED AND (t.hobby_name IN ('Fishing', 'Underwater Diving')) THEN
UPDATE SET remark = concat('Water sports: ', t.remark)
  WHEN MATCHED THEN
DELETE
...

注意事项

MERGE 命令由标准 SQL 清晰定义。命令本身以及之前描述的扩展已由很多 DBMS 实现。不幸的是，大多数实现与标准不同，使用了不同的关键字和/或额外的关键字，有时还使用了不同的概念。即使是介绍性的关键字 MERGE INTO 也会与标准不同。

练习

A) 创建一个新表“contact_merge”，其结构与“contact”相同。
B) 将“contact”中的第 3 行复制到“contact_merge”。
C) 使用 MERGE 命令将“contact”中的所有电子邮件地址插入/更新到“contact_merge”，并将电子邮件协议名称添加到联系值（在“contact_value”列前面添加字符串 'mailto:''）。

单击以查看解决方案

-- Create table and copy one row
CREATE TABLE contact_merge AS SELECT * FROM contact WHERE id = 3;
SELECT * FROM contact_merge;

-- INSERT / UPDATE depending on the column 'id'.
MERGE INTO  contact_merge                             t   -- the target
      USING (SELECT id, person_id, contact_type, contact_value
             FROM   contact
             WHERE  contact_type = 'email')           s   -- the source
      ON    (t.id = s.id)                                 -- the 'match criterion'
  WHEN MATCHED THEN
UPDATE SET contact_value = concat('mailto:', t.contact_value)
  WHEN NOT MATCHED THEN
INSERT (id, person_id, contact_type, contact_value) VALUES (s.id, s.person_id, s.contact_type, concat('mailto:', s.contact_value))
;
COMMIT;
 
-- Check the result
SELECT * FROM contact_merge;

DELETE 2

结构化查询语言
MERGE

TRUNCATE