Oracle中merge into的使用

转载 2013年12月05日 15:42:05

该命令使用一条语句从一个或者多个数据源中完成对表的更新和插入数据. ORACLE 9i 中,使用此命令必须同时指定UPDATE 和INSERT 关键词,ORACLE 10g 做了如下改动。

1,insert 和update是可选的 2,UPDATE 和INSERT 后面可以跟WHERE 子句 3,在ON条件中可以使用常量来insert 所有的行到目标表中,不需要连接到源表和目标表 4,UPDATE 子句后面可以跟delete 来去除一些不需要的行。

举例:

  1. create table PRODUCTS   
  2.     (   
  3.     PRODUCT_ID INTEGER,   
  4.     PRODUCT_NAME VARCHAR2(60),   
  5.     CATEGORY VARCHAR2(60)   
  6.     );   
  7.   
  8.     insert into PRODUCTS values (1501, 'VIVITAR 35MM''ELECTRNCS');   
  9.     insert into PRODUCTS values (1502, 'OLYMPUS IS50''ELECTRNCS');   
  10.     insert into PRODUCTS values (1600, 'PLAY GYM''TOYS');   
  11.     insert into PRODUCTS values (1601, 'LAMAZE''TOYS');   
  12.     insert into PRODUCTS values (1666, 'HARRY POTTER''DVD');   
  13.     commit;   
  14.   
  15.     create table NEWPRODUCTS   
  16.     (   
  17.     PRODUCT_ID INTEGER,   
  18.     PRODUCT_NAME VARCHAR2(60),   
  19.     CATEGORY VARCHAR2(60)   
  20.     );   
  21.   
  22.     insert into NEWPRODUCTS values (1502, 'OLYMPUS CAMERA''ELECTRNCS');   
  23.     insert into NEWPRODUCTS values (1601, 'LAMAZE''TOYS');   
  24.     insert into NEWPRODUCTS values (1666, 'HARRY POTTER''TOYS');   
  25.     insert into NEWPRODUCTS values (1700, 'WAIT INTERFACE''BOOKS');   
  26.     commit;   
  27. 1,可省略的update 或者insert
  28.    MERGE INTO products p   
  29.     2 USING newproducts np   
  30.     3 ON (p.product_id = np.product_id)   
  31.     4 WHEN MATCHED THEN  
  32.     5 UPDATE  
  33.     6 SET p.product_name = np.product_name,   
  34.     7 p.category = np.category;

  使用表newproducts中的product_name 和category字段来更新表products 中相同product_id的product_name 和category.

2,当条件不满足的时候把newproducts表中的数据INSERT 到表products中。

  1. MERGE INTO products p   
  2.     USING newproducts np   
  3.      ON (p.product_id = np.product_id)   
  4.      WHEN NOT MATCHED THEN  
  5.      INSERT  
  6.      VALUES (np.product_id, np.product_name,   
  7.      np.category);   

3,带条件的insert 和update

  1. MERGE INTO products p   
  2.  USING newproducts np   
  3.    ON (p.product_id = np.product_id)   
  4.    WHEN MATCHED THEN  
  5.    UPDATE  
  6.    SET p.product_name = np.product_name   
  7.    WHERE p.category = np.category;

 insert 和update 都带有where 字句

 
  1. MERGE INTO products p   
  2.  USING newproducts np   
  3.     ON (p.product_id = np.product_id)   
  4.     WHEN MATCHED THEN  
  5.     UPDATE  
  6.      SET p.product_name = np.product_name,   
  7.      p.category = np.category   
  8.     WHERE p.category = 'DVD'  
  9.     WHEN NOT MATCHED THEN  
  10.      INSERT  
  11.      VALUES (np.product_id, np.product_name, np.category)   
  12.     WHERE np.category != 'BOOKS'  

4,无条件的insert

  1. MERGE INTO products p   
  2.  USING newproducts np   
  3.   ON (1=0)   
  4.   WHEN NOT MATCHED THEN  
  5.    INSERT  
  6.    VALUES (np.product_id, np.product_name, np.category)   
  7.    WHERE np.category = 'BOOKS'  

5,delete 子句

1  merge into products p
  2  using newproducts np
  3  on(p.product_id = np.product_id)
  4  when matched then
  5  update
  6  set p.product_name = np.product_name
  7  delete where category = 'macle1_cate';

select *

from products;

 PRODUCT_ID PRODUCT_NAME         CATEGORY
--------------------------------------- -------------------- --------------------
                                   1502 macle22              macle2_cate
                                   1503 macle3                macle2_cate
                                   1504 macle                  macle1_cate
                                   1505 macle5                macle5_cate

1504 中的macle1_cate 满足delete where,但是不满足 on 中的条件,所以没有被删除。!!!!!!重点

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

动机:

想在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);

Oracle存储过程之merge into 函数(一)

继上一篇博客:Oracle存储过程游标使用的merge into 函数的使用。 http://blog.csdn.net/z1729734271/article/details/52351700 ...
  • z1729734271
  • z1729734271
  • 2016年08月31日 16:51
  • 3634

Merge into的使用详解-你Merge了没有

Merge是一个非常有用的功能,类似于Mysql里的insert into on duplicate key.  Oracle在9i引入了merge命令,  通过这个merge你能够在一个SQ...
  • z69183787
  • z69183787
  • 2014年05月08日 15:26
  • 4137

merge更新或插入同一张表

一段业务逻辑,需要先判断一条记录在数据库中是否有存在,若存在则更新该记录,若不存在则插入记录。 应用之前的做法是: 1、先用条件判断记录在数据库中的个数。 2.1、若count(*)>0,则执行...
  • bisal
  • bisal
  • 2014年07月28日 18:54
  • 6784

oracle merge into的使用

  • 2012年10月14日 20:11
  • 6KB
  • 下载

Oracle数据库Merge Into 如何使用Sequence 而不“浪费”

1、问题背景:该问题是在做Data Stage时引出来的一个问题,必须使用Merge Into的时候会浪费很多NextVal。 2、测试前提:创建脚本。 -- 创建源表 create table...
  • SmartyIdiot
  • SmartyIdiot
  • 2012年11月18日 15:35
  • 2017

merge into在Oracle中怎么使用

 此命令使用一条语句从一个或者多个数据源中完成对表的更新和插入数据. ORACLE 9i 中,使用此命令必须同时指定UPDATE 和INSERT 关键词,ORACLE 10g 做了如下改动。 ...
  • Q1059081877Q
  • Q1059081877Q
  • 2015年07月12日 09:23
  • 371

工作总结的 -- Oracle merge into 报错& Linux使用

1. Merge into 问题 程序中merge into: 报ORA-30926: 无法在源表中获得一组稳定的行 原因分析:有一数据数源同一时间传输了两条数据,造成merge in...
  • richieruan
  • richieruan
  • 2016年11月02日 17:08
  • 226

Oracle中merge into的使用

该命令使用一条语句从一个或者多个数据源中完成对表的更新和插入数据. ORACLE 9i 中,使用此命令必须同时指定UPDATE 和INSERT 关键词,ORACLE 10g 做了如下改动。   1,...
  • e_wsq
  • e_wsq
  • 2011年12月27日 00:47
  • 307

Oracle 中merge into的使用介绍

http://blog.csdn.net/yuzhic/article/details/1896878 http://blog.csdn.net/macle2010/article/detail...
  • xuzhenxiang
  • xuzhenxiang
  • 2014年07月03日 17:21
  • 460

Oracle-Merge into使用详解

Merge是一个非常有用的功能,类似于MySQL里的insert into on duplicate key.  Oracle在9i引入了merge命令,  通过这个merge你能够在一个...
  • Erica_1230
  • Erica_1230
  • 2017年05月02日 10:55
  • 139
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle中merge into的使用
举报原因:
原因补充:

(最多只允许输入30个字)