SQL DML合并数据|极客笔记

在 SQL:2003 标准中引入了一个新的数据操作语句：MERGE。MERGE 可以同时完成 INSERT 和 UPDATE 的操作，甚至 DELETE 的功能。

目前只有 Oracle 和SQL Server 支持 MERGE 语句，简单的 MERGE 语法如下：

-- Oracle 和 SQL Server 实现
MEGRE INTO target_table [AS t_alias] 
USING source_table [AS s_alias]
   ON (condition)
 WHEN MATCHED THEN
      UPDATE SET column1 = expr_1,
                 column2 = expr_2,
                 ...
 WHEN NOT MATCHED THEN
      INSERT (column1, column2, ...)
      VALUES (expr_1, expr_2, ...);

其中，

target_table 是合并操作的目标表；
USING 指定了数据的来源，可以是一个表或者查询结果；
ON 指定了合并的条件，通常是主键或者唯一键相等；
对于数据源中的每一行，如果在目标表中存在满足条件的记录，执行 WHEN MATCHED THEN 分支更新目标表中对应的记录；如果不存在匹配的记录，执行 WHEN NOT MATCHED THEN 分支在目标表中插入一条新记录。

我们先创建一个用于数据合并的目标表 emp_merge：

CREATE TABLE emp_merge
    ( emp_id   INTEGER NOT NULL PRIMARY KEY
    , emp_name VARCHAR(50) NOT NULL
    , sex      VARCHAR(10) NOT NULL
    , salary   NUMERIC(8,2) NOT NULL
    , email    VARCHAR(100) NOT NULL
    );

以下示例使用 MERGE 语句将员工表中女性员工的信息合并到 emp_merge 表中：

-- Oracle 和 SQL Server 实现
MERGE INTO emp_merge t
USING (SELECT emp_id, emp_name, sex, salary, email
         FROM employee
        WHERE sex = '女') s
   ON (t.emp_id = s.emp_id)
 WHEN MATCHED THEN
      UPDATE
         SET t.emp_name = s.emp_name,
             t.sex      = s.sex,
             t.salary   = s.salary,
             t.email    = s.email
 WHEN NOT MATCHED THEN
      INSERT (emp_id, emp_name, sex, salary, email)
      VALUES (s.emp_id, s.emp_name, s.sex, s.salary, s.email);

其中，合并的条件为 emp_id 是否相同；相同则更新已有的数据，不同则插入数据。第一次运行以上语句时，由于 emp_merge 表中没有任何数据，因此执行的都是 WHEN NOT MATCHED THEN 分支，即插入新的数据。

SELECT *
  FROM emp_merge;

EMP_ID|EMP_NAME|SEX|SALARY|EMAIL                   |
------|--------|---|------|------------------------|
     7|孙尚香   |女  | 12000|sunshangxiang@shuguo.com|
     8|孙丫鬟   |女  |  6000|sunyahuan@shuguo.com    |
    12|赵氏     |女  |  6600|zhaoshi@shuguo.com      |

大家可以尝试修改 emp_merge 中的某些数据，然后再次运行上面的 MERGE 语句，看看 WHEN MATCHED THEN 分支的更新结果。

MERGE 语句也可以只包含更新或者插入操作，例如：

-- Oracle 和 SQL Server 实现
MERGE INTO emp_merge t
USING (SELECT emp_id, emp_name, sex, salary, email
         FROM employee
        WHERE sex = '女') s
   ON (t.emp_id = s.emp_id)
 WHEN NOT MATCHED THEN
      INSERT (emp_id, emp_name, sex, salary, email)
      VALUES (s.emp_id, s.emp_name, s.sex, s.salary, s.email);

以上语句只在没有匹配数据时插入新的数据，而不执行更新操作。

另外，MERGE 还支持 DELETE 功能，用于在目标表中找到匹配的数据时删除记录：

-- Oracle 实现
MERGE INTO emp_merge t
USING (SELECT emp_id, emp_name, sex, salary, email
         FROM employee
        WHERE sex = '女') s
   ON (t.emp_id = s.emp_id)
 WHEN MATCHED THEN
      UPDATE
         SET t.emp_name = s.emp_name,
             t.sex      = s.sex,
             t.salary   = s.salary,
             t.email    = s.email
      DELETE WHERE t.emp_name = '赵氏'
 WHEN NOT MATCHED THEN
      INSERT (emp_id, emp_name, sex, salary, email)
      VALUES (s.emp_id, s.emp_name, s.sex, s.salary, s.email);

-- SQL Server 实现
MERGE INTO emp_merge t
USING (SELECT emp_id, emp_name, sex, salary, email
         FROM employee
        WHERE sex = '女') s
   ON (t.emp_id = s.emp_id)
 WHEN MATCHED AND t.emp_name = '赵氏' THEN 
      DELETE
 WHEN MATCHED THEN
      UPDATE
         SET t.emp_name = s.emp_name,
             t.sex      = s.sex,
             t.salary   = s.salary,
             t.email    = s.email
 WHEN NOT MATCHED THEN
      INSERT (emp_id, emp_name, sex, salary, email)
      VALUES (s.emp_id, s.emp_name, s.sex, s.salary, s.email);

Oracle 和 SQL Server 对于 DELETE 的实现略有不同，但都是在 MATCHED 分支增加了一个删除操作。如果运行以上语句，emp_merge 表中姓名为“赵氏”的数据将会被删除。

MySQL 和 PostgreSQL 没有提供 MERGE 语句，而是使用专有的语法格式：

-- MySQL 实现
INSERT INTO emp_merge(emp_id, emp_name, sex, salary, email)
SELECT emp_id, emp_name, sex, salary, email
  FROM employee s
 WHERE sex = '女'
    ON DUPLICATE KEY UPDATE
       emp_name = s.emp_name,
       sex      = s.sex,
       salary   = s.salary,
       email    = s.email;

-- PostgreSQL 实现
INSERT INTO emp_merge(emp_id, emp_name, sex, salary, email)
SELECT emp_id, emp_name, sex, salary, email
  FROM employee
 WHERE sex = '女'
    ON CONFLICT(emp_id) DO UPDATE
   SET emp_name = EXCLUDED.emp_name,
       sex      = EXCLUDED.sex,
       salary   = EXCLUDED.salary,
       email    = EXCLUDED.email;

MySQL 中 ON DUPLICATE KEY UPDATE 表示主键或者唯一索引出现重复值时执行更新操作；PostgreSQL 中 ON CONFLICT(emp_id) DO UPDATE 表示 emp_id 重复时执行更新操作，EXCLUDED 代表了此时的源数据。这两种语法不支持 DELETE 操作。