merge into

最新推荐文章于 2024-12-26 00:12:14 发布

csdn1284934570

最新推荐文章于 2024-12-26 00:12:14 发布

阅读量431

点赞数 1

CC 4.0 BY-SA版权

分类专栏： oracle

本文链接：https://blog.youkuaiyun.com/csdn1284934570/article/details/47074891

oracle 专栏收录该内容

50 篇文章

订阅专栏

本文详细解析了SQL中的MERGE语句，包括其语法、实例演示及应用场景。通过具体操作如更新匹配记录、插入未匹配记录等，深入理解如何在数据库间进行高效数据同步。

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

一、语法:

Merge Into dest_table alais_name Using src_table alias_name On( condition )
When Matched Then Update Set ...[where...] [Delete Where....] 注:delete的作用范围为update过的那些记录
When Not Matched Then Insert (column_name1,...) values(column_value1,...)....[Where ....]

二、实例

1、创建测试数据

SQL> Create Table tmp1(Id Number,Name Varchar2(20))
2 /
Table created
SQL> Create Table tmp2(Id Number,Name Varchar2(20))
2 /
Table created
SQL> Insert Into tmp1
2 Select 1,'张一' From dual Union All
3 Select 2,'张二' From dual Union All
4 Select 3,'张三' From dual
5 /
3 rows inserted
SQL> Insert Into tmp2
2 Select 1,'李一' From dual Union All
3 Select 2,'李二' From dual Union All
4 Select 3,'李三' From dual Union All
5 Select 4,'李四' From dual Union All
6 Select 5,'李五' From dual
7 /
5 rows inserted

SQL>

2、使用when matched then update ...where

SQL> Merge Into tmp1 a Using tmp2 b On(a.id=b.id)
2 When Matched Then Update Set a.name=b.name Where b.id<=2 ;
2 rows merged

SQL> select * from tmp1;
        ID NAME
---------- --------------------
         1 李一
         2 李二
         3 张三

SQL> rollback;
Rollback complete

3、使用when not matched then insert

SQL> Merge Into tmp1 a Using tmp2 b On(a.id=b.id)
2 When Not Matched Then Insert(Id,Name) Values(b.id,b.name);
2 rows merged

SQL> select * from tmp1;
        ID NAME
---------- --------------------
         1 张一
         2 张二
         3 张三
         5 李五
         4 李四

SQL> rollback;
Rollback complete

4、使用when not matched then insert...where

SQL> Merge Into tmp1 a Using tmp2 b On(a.id=b.id)
2 When Not Matched Then Insert Values(b.id,b.name) Where b.id>=5;
1 row merged

SQL> select * from tmp1;
        ID NAME
---------- --------------------
         1 张一
         2 张二
         3 张三
         5 李五

SQL> rollback;
Rollback complete

5 、使用when matched then update where delete where

SQL> Merge Into tmp1 a Using tmp2 b On(a.id=b.id)
2 When Matched Then Update Set a.name=b.name Where b.id<=2 Delete Where a.id=1;
2 rows merged

SQL> select * from tmp1;
        ID NAME
---------- --------------------
         2 李二
         3 张三