merge into

一、语法:

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