merge into

来源：互联网发布：优化驱动器要不要开编辑：程序博客网时间：2024/04/30 00:25

一、语法:

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 张三

0 0