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 操作。

赞(2)
未经允许不得转载:极客笔记 » SQL DML合并数据
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址