SQL merge用法

merge主要用于两表之间的关联操作

 从oracle 9i开始支持merge用法，10g有了完善

create table a (id_ integer,count_ integer);
insert into a values(1,3);
insert into a values(3,6);

create table b (id_ integer,count_ integer);
insert into b values(1,7);
insert into b values(2,4);

MERGE INTO a
    USING b
    ON (a.id_ = b.id_)
WHEN MATCHED THEN
   UPDATE SET count_ = b.count_+a.count_  /* 注意指名count_属于的表 */
WHEN NOT MATCHED THEN
   INSERT VALUES (b.id_,b.count_);

commit;
select * from a;

结果：

    id_   count_

    1        10

    3          6

    2          4