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