Oracle merge into 的用法详解实例

作用：merge into 解决用B表跟新A表数据，如果A表中没有，则把B表的数据插入A表；

--  语法：

--  MERGE INTO [your table-name] [rename your table here]

--  USING ( [write your query here] )[rename your query-sql and using just like a table]

--  ON ([conditional expression here] AND [...]...)

--  WHEN MATHED THEN [here you can execute some update sql or something else ]

--  WHEN NOT MATHED THEN [execute something else here ! ]

-------实例-------

drop table a;
drop table b;
create table a(id number(1),name varchar2(10));
create table b(id number(1),name varchar2(10));
insert into a values(1,'2');
insert into b values(1,'1');
insert into b values(2,'2');
commit;

merge into  a
using (select id,name from b ) c
on(a.id=c.id )
when not matched then insert (a.id,a.name) values (c.id,c.name);


merge into  a
using (select id,name from b ) c
on(a.id=c.id )
when matched then update set a.name=c.name;

merge into  a
using (select id,name from b ) c
on(a.id=c.id )
when matched then update set a.name=c.name
when not matched then insert (a.id,a.name) values (c.id,c.name);


作用：将表 b 数据 更新到 表a ，条件是a.id=b.id，如果a表中有该条件的数据就修改，没有该条件的数据就插入。

如果你的数据量很大，此sql效率非常高。