Merge into

在 SQL 中，Merge into 是一个用于合并数据的命令，通常用于将数据从一个表（源表）合并到另一个表（目标表）。它结合了 insert、update 和 delete 三种操作，允许在单个 SQL 语句中根据匹配条件来插入、更新或删除记录。

Merge into 在许多数据库系统中都有支持，例如：Oracle、SQL Server、PostgreSQL 等。

另外，执行 Merge into 语句后，通常需要显式地执行 commit 来提交事务，才能使更改永久生效。

语法

Merge into 语句的基本框架如下：

MERGE INTO 目标表名 [别名]
USING 源表或子查询 [别名]
ON (匹配条件)
WHEN MATCHED THEN 
    UPDATE SET 目标表列1 = 源值1 [, 目标表列2 = 源值2 ...]
    [WHERE 条件] 
    [DELETE WHERE 条件] 
WHEN NOT MATCHED THEN 
    INSERT (目标表列1 [, 目标表列2 ...])
    VALUES (源值1 [, 源值2 ...])
    [WHERE 条件];

关键部分解析：

●MERGE INTO 目标表：指定要进行插入或更新操作的目标表。
●USING 源表或子查询：指定数据来源，可以是一个表、视图或者一个子查询（subquery）。
●ON (匹配条件)：这是连接目标表和源数据的关键，它定义了如何判断源数据中的一条记录在目标表中是否“存在”（例如 ON (目标表.员工ID = 源表.员工ID)）。此处的字段最好是唯一键，以避免重复匹配的问题。
●WHEN MATCHED THEN：当ON子句中的条件匹配时（即目标表中已存在该记录），执行 UPDATE 操作，你可以在 UPDATE SET后指定要更新哪些列。从 Oracle 10g 开始，还可以使用 WHERE 子句为更新操作附加条件
，以及使用 DELETE WHERE 在更新后删除满足特定条件的记录（此 DELETE操作仅适用于匹配且被更新后的行）。
●WHEN NOT MATCHED THEN：当ON子句中的条件不匹配时（即目标表中不存在该记录），执行 INSERT 操作。你需要指定要插入的列 (INSERT (列1, 列2…)) 以及这些列对应的值 (VALUES (值1, 值2…))。同样地，从 Oracle 10g 开始，也可以为插入操作添加 WHERE 条件。

使用场景

MERGE INTO语句特别适用于以下场景：

●数据同步：定期将某个数据源（如临时表、操作表）的变化同步到主表或数据仓库中。
●批量数据处理：高效地处理大量数据的插入和更新，减少代码复杂性和数据库往返次数。
●数据初始化或迁移：在系统初始化或数据迁移时，将新数据合并到现有表中，避免重复插入。

示例

假设我们有一个 employees 表（目标表）和一个 new_employees 表（源表），如果 employees 表中已存在相同的 employee_id，并且新表中的 salary 更高，则更新该员工的姓名和工资。如果 employees 表中不存在相同的 employee_id，则插入一条新记录。

MERGE INTO employees e
USING new_employees n
ON (e.employee_id = n.employee_id)
WHEN MATCHED THEN
    UPDATE SET 
        e.name = n.name,
        e.salary = n.salary
    WHERE n.salary > e.salary -- 仅当新工资更高时更新
WHEN NOT MATCHED THEN
    INSERT (e.employee_id, e.name, e.salary, e.department)
    VALUES (n.employee_id, n.name, n.salary, n.department);

1、省略 INSERT 子句（只更新，不插入）

merge into products p
 using newproducts np
 on (p.product_id = np.product_id) 
 when matched then update
set p.product_name = np.product_name,
    p.category = np.category;

解释：

●功能：只执行更新操作，不执行插入操作
●逻辑：当products和newproducts表的 product_id 匹配时，用新产品的名称和类别更新现有产品
●使用场景：批量更新已有记录，不需要添加新记录时
●注意：完全省略了 WHEN NOT MATCHED THEN 子句

2、省略 UPDATE 子句（只插入，不更新）

merge into products p
 using newproducts np
 on (p.product_id = np.product_id)
 when not matched then
insert
 values (np.product_id, np.product_name, np.category);

解释：

●功能：只执行插入操作，不执行更新操作
●逻辑：当在目标表中找不到匹配的产品ID时，插入新记录
●使用场景：只添加新记录，不修改现有记录时
●注意：完全省略了 WHEN MATCHED THEN 子句

3、带条件的 UPDATE

merge into products p
 using newproducts np
 on (p.product_id = np.product_id) 
 when matched then update
set p.product_name = np.product_name
 where p.category = np.category;

解释：

●功能：有条件地执行更新操作
●逻辑：只有当新旧产品的类别相同时，才更新产品名称
●使用场景：需要根据特定条件决定是否更新时
●注意：WHERE子句在 UPDATE之后，用于过滤要更新的记录

4、UPDATE 和 INSERT 都带条件

merge into products p
 using newproducts np
 on (p.product_id = np.product_id)
 when matched then update
set p.product_name = np.product_name, 
    p.category = np.category 
 where p.category = 'DVD'
 when not matched then
insert
 values (np.product_id, np.product_name, np.category)
 where np.category != 'BOOKS';

解释：

●功能：更新和插入操作都有附加条件
●逻辑：只更新类别为 ‘DVD’ 的现有产品，只插入类别不是 ‘BOOKS’ 的新产品
●使用场景：需要同时对更新和插入操作进行复杂条件过滤时

5、无条件 INSERT（巧妙用法）

MERGE INTO products p
 USING newproducts np
 ON (1=0)  -- 永远为false的条件
 WHEN NOT MATCHED THEN
INSERT
 VALUES (np.product_id, np.product_name, np.category)
 WHERE np.category = 'BOOKS'

解释：

●功能：实现有条件的纯插入操作
●技巧：ON (1=0) 确保连接条件永远不成立，因此所有记录都会进入 NOT MATCHED 分支
●逻辑：只插入类别为 ‘BOOKS’ 的新产品
●使用场景：需要基于源表数据有条件地插入记录，且绝对不需要更新时
●优势：比单纯的 INSERT…SELECT 语句更易读和维护

6、使用 DELETE 子句

merge into products p
 using newproducts np
 on(p.product_id = np.product_id)
 when matched then update
set p.product_name = np.product_name
 delete where category = 'macle1_cate'

解释：

●功能：在更新后执行删除操作
●逻辑：先更新匹配的记录，然后删除类别为 ‘macle1_cate’ 的记录
●重要特性：DELETE子句只能与 UPDATE子句一起使用，DELETE WHERE是在更新操作之后执行的，它只删除那些在本次 merge 操作中被更新过的记录，删除的是目标表中的记录，不是源表中的记录
●使用场景：需要在数据合并后清理特定记录时