Oracle Merge Into 的用法详解实例

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 ! ]

 

 

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

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效率非常高。