merge 用法示例

//测试表以及数据
//product_types:产品类型表
select * from product_types;
      PRODUCT_TYPE_ID NAME
--------------------- ----------
                    1 Book
                    2 Video
                    3 DVD
                    4 CD
                    5 Magazine
//products:产品信息表
select * from products;
      PRODUCT_ID          PRODUCT_TYPE_ID NAME            DESCRIPTION                       PRICE
---------------- ------------------------ --------------- ------------------------------- -------
               2                        1 Chemistry       Introduction to Chemistry         30.00
               3                        2 Supernova       A star explodes                   25.99
               4                        2 Tank War        Action movie about a future war   13.95
               1                        1 Modern Science  A description of modern science   19.95
//product_changes:产品信息变更表
create table product_changes(
             product_id integer primary key,
             product_type_id integer references product_types(product_type_id),
             name varchar2(50),
             price number(5,2))
//变更数据
select * from product_changes;
       PRODUCT_ID           PRODUCT_TYPE_ID NAME                                   PRICE
----------------- ------------------------- ------------------------------------ -------
                1                         1 Modern Science                         40.00
                2                         1 New Chemistry                          35.00
                3                         1 Supernova                              25.99
               13                         2 Lunar Landing                          15.99
               14                         2 Submarine                              15.99
               15                         2 Airplane                               15.99
               16                         5 Programmer                             20.00
//我想要做的是，将product_changes表中的数据跟新到products表中；
//1.更新products表中原有的数据，2.如果products表中没有的数据，就添加进去
//下面是具体实现代码:
merge into products p
using product_changes pc
on (p.product_id=pc.product_id)
when matched then
     update
     set p.product_type_id=pc.product_type_id,
         p.name=pc.name,
         p.price=pc.price
when not matched then
     insert
     (p.product_id,p.product_type_id,p.name,p.price)
     values
     (pc.product_id,pc.product_type_id,pc.name,pc.price);
//操作完成后，查看一下products表中产品信息，
//我们发现，原有的数据部分被更新了，而且新添加了一条原来没有的数据
select product_id,product_type_id,name,price
from products;
              PRODUCT_ID               PRODUCT_TYPE_ID NAME                  PRICE
------------------------ ----------------------------- ------------------- -------
                       2                             1 New Chemistry         35.00
                       3                             1 Supernova             25.99
                       4                             2 Tank War              13.95
                       1                             1 Modern Science        40.00
                      13                             2 Lunar Landing         15.99
                      15                             2 Airplane              15.99
                      14                             2 Submarine             15.99
                      16                             5 Programmer            20.00
//merge into子句常用于几个关联表更新表，和批量加载数据。