merge用法详解

最新推荐文章于 2025-06-29 16:19:26 发布

原创最新推荐文章于 2025-06-29 16:19:26 发布 · 1.1w 阅读

5 ·

CC 4.0 BY-SA版权

数据库专栏收录该内容

14 篇文章

订阅专栏

本文详细介绍了 SQL 中 MERGE 语句的各种使用场景，包括普通更新插入、添加额外条件、忽略某些条件、抛出异常及删除操作等。通过具体的例子展示了如何灵活运用 MERGE 语句来实现数据同步和更新。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

merge用法详解

1、普通用法：

MERGE INTO EMPLOYE AS EM
USING (SELECT * FROM MANAGER WHERE MA.SALARY > 2) AS MA
ON (EM.EMPLOYEID = MA.MANAGERID)
WHEN MATCHED AND EM.SALARY < MA.SALARY THEN UPDATE SET EM.SALARY = MA.SALARYWHEN NOT MATCHED THEN
  INSERT VALUES (MA.MANAGERID, MA.NAME, MA.SALARY);

2、添加其它条件：

MERGE INTO EMPLOYE AS EM
USING (SELECT * FROM MANAGER WHERE MA.SALARY > 2) AS MA
ON (EM.EMPLOYEID = MA.MANAGERID)
WHEN MATCHED AND EM.SALARY < MA.SALARY THEN 
UPDATE SET EM.SALARY = MA.SALARYWHEN
 NOT MATCHED THEN
  INSERT VALUES (MA.MANAGERID, MA.NAME, MA.SALARY);

3、忽略某个条件

MERGE INTO EMPLOYE AS EM
USING MANAGER AS MA
ON EM.EMPLOYEID = MA.MANAGERID 
WHEN MATCHED AND EM.SALARY < MA.SALARY THEN 
 UPDATE SET EM.SALARY = MA.SALARYWHEN 
 NOT MATCHED THEN
  INSERT VALUES (MA.MANAGERID, MA.NAME, MA.SALARY) 
 ELSE IGNORE

4、抛异常:

MERGE INTO EMPLOYE AS EM
USING MANAGER AS MA
ON EM.EMPLOYEID = MA.MANAGERID
WHEN MATCHED THEN AND EM.SALARY < MA.SALARY THEN 
UPDATE SET EM.SALARY = MA.SALARY
WHEN MATCHED AND EM.SALARY > MA.SALARY THEN 
SIGNAL SQLSTATE '70001' SET MESSAGE_TEXT = 'EM.SALARY > MA.SALARY'
WHEN NOT MATCHED THEN
  INSERT VALUES (MA.MANAGERID, MA.NAME, MA.SALARY);

5、删除:

MERGE INTO EMPLOYE AS EM USING MANAGER MA ON EM.EMPLOYEID = MA.MANAGERID 
WHEN MATCHED AND EM.SALARY < MA.SALARY THEN
   UPDATE SET EM.SALARY = MA.SALARY
WHEN MATCHED AND EM.SALARY > MA.SALARY THEN 
   DELETE
WHEN NOT MATCHED THEN 
   INSERT VALUES (MA.MANAGERID, MA.NAME, MA.SALARY);