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

 

如果A表数据量比较大，一定要避免对A表进行全表扫描！

应该以子查询C为驱动表，再以索引方式去访问A表！

 

对A表id字段创建索引，同时C表的id字段创建索引！

 

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

 

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 tfa_alarm_act_nms a using (select FP0,FP1,FP2,FP3,REDEFINE_SEVERITY from tfa_alarm_status) b on (a.fp0=b.fp0 and a.fp1=b.fp1 and a.fp2=b.fp2 and a.fp3=b.fp3) when matched then update set a.redefine_severity=b.redefine_severity when not matched then insert (a.fp0,a.fp1,a.fp2,a.fp3,a.org_severity,a.redefine_severity,a.event_time ,a.int_id) values (b.fp0,b.fp1,b.fp2,b.fp3,b.REDEFINE_SEVERITY,b.redefine_severity,sysdate,7777778); 作用：利用表 tfa_alarm_status跟新表tfa_alarm_act_nms的b.redefine_severity，条件是a.fp0=b.fp0 and a.fp1=b.fp1 and a.fp2=b.fp2 and a.fp3=b.fp3，如果tfa_alarm_act_nms表中没有该条件的数据就插入。 如果你的数据量很大，此sql效率非常高。

 

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

MERGE INTO T T1
USING (SELECT '1001' AS a,2 AS b FROM dual) T2
ON ( T1.a=T2.a)
WHEN MATCHED THEN
  UPDATE SET T1.b = T2.b
WHEN NOT MATCHED THEN 
  INSERT (a,b) VALUES(T2.a,T2.b);

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

Oracle merge into ，需要注意的地方！

merge into 是特有的功能，相当于在 MSSQL中的

if exists(...)

   update table

else

   Insert into table.

merge into 语法不仅没有if exists语法啰嗦，而且比if exists 还要高效很多。我经常用来在oracle之间同步数据库表。

语法如下：

MERGE INTO table_name alias1
USING (table|view|sub_query) alias2
ON (join condition)
WHEN MATCHED THEN
UPDATE table_name
SET col1 = col_val1,
col2 = col2_val
WHEN NOT MATCHED THEN
INSERT (column_list) VALUES (column_values);

1、

从语法条件上看（ ON (join condition) ），merge into也是一个危险的语法。如果不慎重使用，会将alias2 表全部覆盖alias1，既危险又浪费效率，违背了增量同步的原则。我在设计表结构中，一般每条记录都有“更新时间”的字段，用alias1“最大更新时间”判断alias2 数据是否有更新和新增的信息。须谨慎控制alias2 条件。

2、更新的字段，不允许有关联条件的字段（join condition）。比如条件是 A.ID=B.ID，那么使用“SET A.ID=B.ID”将报出一个莫名其妙的提示错误。
 

 

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

Merge Into 语句代替Insert/Update在Oracle中的应用实战：

 

动机：

想在Oracle中用一条SQL语句直接进行Insert/Update的操作。

说明：

在进行SQL语句编写时，我们经常会遇到大量的同时进行Insert/Update的语句 ，也就是说当存在记录时，就更新(Update)，不存在数据时，就插入(Insert)。

实战：

接下来我们有一个任务，有一个表T，有两个字段a,b，我们想在表T中做Insert/Update,如果存在，则更新T中b的值，如果不存在，则插入一条记录。在Microsoft的SQL语法中，很简单的一句判断就可以了，SQL Server中的语法如下：

if exists(select 1 from T where T.a='1001' ) update T set T.b=2 Where T.a='1001' else insert into T(a,b) values('1001',2);

以上语句表明当T表中如果存在a='1001' 的记录的话，就把b的值设为2，否则就Insert一条a='100'，b=2的记录到T中。

但是接下来在Oracle中就遇到麻烦了，记得在Oracle 9i之后就有一条Merge into 的语句可以同时进行Insert 和Update的吗，Merge的语法如下：

 

MERGE INTO table_name alias1 
USING (table|view|sub_query) alias2
ON (join condition) 
WHEN MATCHED THEN 
    UPDATE table_name 
    SET col1 = col_val1, 
        col2     = col2_val 
WHEN NOT MATCHED THEN 
    INSERT (column_list) VALUES (column_values); 

 

 

上面的语法大家应该都容易懂吧，那我们按照以上的逻辑再写一次。

 

MERGE INTO T T1
USING (SELECT a,b FROM T WHERE t.a='1001') T2
ON ( T1.a=T2.a)
WHEN MATCHED THEN
  UPDATE SET T1.b = 2
WHEN NOT MATCHED THEN 
  INSERT (a,b) VALUES('1001',2);

 

以上的语句貌似很对是吧，实际上，该语句只能进行更新，而无法进行Insert，错误在哪里呢？

其实在Oracle中Merge语句原先是用来进行整表的更新用的，也就是ETL工具比较常用的语法，重点是在Using上。

用中文来解释Merge语法，就是：

在alias2中Select出来的数据，每一条都跟alias1进行 ON (join condition)的比较，如果匹配，就进行更新的操作(Update),如果不匹配，就进行插入操作(Insert)。

因此，严格意义上讲，”在一个同时存在Insert和Update语法的Merge语句中，总共Insert/Update的记录数，就是Using语句中alias2的记录数。”

以上这句话也就很好的解释了在上面写的语句为何只能进行Update，而不能进行Insert了，因为都Select不到数据，如何能进行Insert呢:)

接下来要改成正确的语句就容易多了，如下：

 

MERGE INTO T T1
USING (SELECT '1001' AS a,2 AS b FROM dual) T2
ON ( T1.a=T2.a)
WHEN MATCHED THEN
  UPDATE SET T1.b = T2.b
WHEN NOT MATCHED THEN 
  INSERT (a,b) VALUES(T2.a,T2.b);

 

查询结果，OK!

注意：

如果不懂Merge语句的原理，Merge语句是一条比较危险的语句，特别是在您只想更新一条记录的时候，因为不经意间，你可能就把整表的数据都Update了一遍.....汗!!!

我曾经犯过的一个错误如下所示，大家看出来是什么问题了吗？

 

MERGE INTO T T1
USING (SELECT Count(*) cnt FROM T WHERE T.a='1001') T2
ON (T2.cnt>0)
WHEN MATCHED THEN
  UPDATE SET T1.b = T2.b
WHEN NOT MATCHED THEN 
  INSERT (a,b) VALUES(T2.a,T2.b);

 

 

from:

http://www.cnblogs.com/xy6521/articles/1310177.html

http://www.cnblogs.com/kingkoo/archive/2009/03/03/1402536.html

http://hi.baidu.com/tintinsoft/blog/item/c2fd690f86da9cc37acbe177.html

http://blog.csdn.net/tianlesoftware/article/details/4714921

http://blog.csdn.net/bruesz/article/details/1894836