`

Oracle中merge into的使用

阅读更多
引自:http://www.cnblogs.com/highriver/archive/2011/08/02/2125043.html

引言
  这篇文章不错,最后的方法解决了我的一个问题。原来使用H2数据库,使用Merge into,能自动实现更新或插入,到了Oracle的merge into,用起来不一样,同样的语句,在两个库中不是解决一个问题的。 

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

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

举例:

create table PRODUCTS(  
    PRODUCT_ID INTEGER,  
    PRODUCT_NAME VARCHAR2(60),  
    CATEGORY VARCHAR2(60)  
    );  
 
    insert into PRODUCTS values (1501, 'VIVITAR 35MM', 'ELECTRNCS');  
    insert into PRODUCTS values (1502, 'OLYMPUS IS50', 'ELECTRNCS');  
    insert into PRODUCTS values (1600, 'PLAY GYM', 'TOYS');  
    insert into PRODUCTS values (1601, 'LAMAZE', 'TOYS');  
    insert into PRODUCTS values (1666, 'HARRY POTTER', 'DVD');  
    commit;  
 
    create table NEWPRODUCTS(  
    PRODUCT_ID INTEGER,  
    PRODUCT_NAME VARCHAR2(60),  
    CATEGORY VARCHAR2(60)  
    );  
 
    insert into NEWPRODUCTS values (1502, 'OLYMPUS CAMERA', 'ELECTRNCS');  
    insert into NEWPRODUCTS values (1601, 'LAMAZE', 'TOYS');  
    insert into NEWPRODUCTS values (1666, 'HARRY POTTER', 'TOYS');  
    insert into NEWPRODUCTS values (1700, 'WAIT INTERFACE', 'BOOKS');  
    commit;  

1,可省略的update 或者insert
   MERGE INTO products p  
    USING newproducts np  
    ON (p.product_id = np.product_id)  
    WHEN MATCHED THEN 
    UPDATE 
    SET p.product_name = np.product_name,  
    p.category = np.category;
  使用表newproducts中的product_name 和category字段来更新表products 中相同product_id的product_name 和category.

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

MERGE INTO products p  
    USING newproducts np  
     ON (p.product_id = np.product_id)  
     WHEN NOT MATCHED THEN 
     INSERT 
     VALUES (np.product_id, np.product_name,  
     np.category); 

3,带条件的insert 和update

MERGE INTO products p  
USING newproducts np  
   ON (p.product_id = np.product_id)  
   WHEN MATCHED THEN 
   UPDATE 
   SET p.product_name = np.product_name  
   WHERE p.category = np.category;
insert 和update 都带有where 字句

MERGE INTO products p  
USING newproducts np  
    ON (p.product_id = np.product_id)  
    WHEN MATCHED THEN 
    UPDATE 
     SET p.product_name = np.product_name,  
     p.category = np.category  
    WHERE p.category = 'DVD' 
    WHEN NOT MATCHED THEN 
     INSERT 
     VALUES (np.product_id, np.product_name, np.category)  
    WHERE np.category != 'BOOKS' 

4,无条件的insert

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

5,delete 子句

merge into products p
  using newproducts np
  on(p.product_id = np.product_id)
  when matched then
  update
  set p.product_name = np.product_name
  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 的使用 描述详解:Oracle Merge Into 是一个功能强大的SQL语句,它在Oracle数据库系统中被广泛用于处理数据同步、更新或插入操作。与传统的UPDATE和INSERT语句相比,Merge Into 提供了更为...

    浅谈Oracle数据库中Merge Into的用法.pdf

    Oracle 数据库中 Merge Into 的用法 在 Oracle 数据库中,Merge Into 语句是一种非常有用的功能,它主要用来合并 update 和 insert 语句,即用一个表中的数据来修 改或插入到另一个表中。Merge Into 语句的主要原则...

    Oracle merge into用法

    在Oracle数据库中,`MERGE INTO`语句是一种非常强大的工具,它允许用户根据源表中的数据更新目标表,同时也可以插入新记录。这一特性在处理大量数据时尤其有用,因为它能够在一个步骤中完成更新和插入操作,从而提高...

    Mybatis批量foreach merge into的用法

    首先,需要使用Oracle 9i及其以后版本支持的merge into语句,该语句可以实现insertOrUpdate的功能。然后,使用Mybatis的动态SQL语法foreach循环插入,待插入的实体bean的List通过查询数据库dual形成表。foreach的 ...

    oracle中merge into用法及实例解析

    Oracle中的`MERGE INTO`语句是一种强大的工具,用于整合数据操作,如更新、插入和删除,...需要注意的是,`MERGE INTO`可能会触发并发控制问题,因此在多用户环境中使用时需要谨慎,确保事务处理和并发控制策略适当。

    oracle死锁原因解决办法

    在Oracle数据库系统中,死锁是一种常见的并发问题,它会导致多个事务之间互相等待对方释放资源而无法继续执行,最终导致整个系统的运行效率降低甚至停滞。死锁不仅影响数据库性能,还会影响应用程序的可用性和用户...

    oracle merge 用法详解

    3. **支持常量过滤谓词**:可以在`ON`条件中使用常量过滤谓词,这意味着即使不连接源表和目标表也可以插入所有行到目标表中。 4. **支持DELETE子句**:`MERGE`语句还可以跟`DELETE`子句结合使用,以便删除某些不符合...

    Oracle_merge

    本文将详细介绍 Oracle 中 `MERGE` 关键字的使用方法,并结合实际示例来解析其工作原理。 #### 二、`MERGE` 语句的基本概念 在 Oracle 9i R2 版本之前,如果需要同时进行更新和插入操作,开发人员通常需要编写两条...

    使用BULK COLLECT, MERGE 语句提高sql执行效率

    详细介绍了使用 BULK COLLECT 进行批量操作 提高sql的执行效率 使用MERGE INTO USING 一条sql搞定 新增和修改 使用connect by 进行递归树查询

    利用 sqluldr2导出数据 使用sqlldr导入数据 通过merge into 合并更新数据

    下面将详细阐述如何使用这两个工具以及如何通过`MERGE INTO`语句进行数据合并更新。 1. SQLULDR2导出数据: SQLULDR2是Oracle提供的一款数据导出工具,它能够快速地将数据库中的数据导出到文本文件或其他格式的...

    oracle Merge 函数.doc

    Oracle Merge 函数是一种强大的数据操作语句,用于从一个表中选择一些数据更新或者插入到另一个表中。Merge 函数的使用方式有三种:只更新不插入、只插入不更新和既插入也更新。 Merge 函数的基本用法 Merge ...

    Oracle MERGE INTO的用法示例介绍

    如果不使用Oracle提供的merge语法的话,可能先要上数据库select查询一下看是否存在,然后决定怎么操作,这样的话需要写更多的代码,同时性能也不好,要来回数据库两次.使用merge的话则可以一条SQL语句完成. 1)主要功能 ...

    oracle数据匹配merge into的实例详解

    Oracle的`MERGE INTO`语句是一种强大的工具,用于根据源数据更新或插入目标表的记录。这个SQL命令结合了`INSERT`, `UPDATE`和`DELETE`操作,使得在处理数据匹配和同步时更加高效。在给定的实例中,`MERGE INTO`被...

    MySQL中实现插入或更新操作(类似Oracle的merge语句)

    如果需要在MySQL中实现记录不存在则insert,不存在则update操作。可以使用以下语句: 更新一个字段: INSERT INTO tbl (columnA,columnB,columnC) VALUES (1,2,3) ON DUPLICATE KEY UPDATE columnA=IF(columnB>0,...

    oracle merge

    merge 详解 及实例 优点:  — 避免了分开更新  — 提高性能并易于使用  — 在数据仓库应用中十分有用  — 使用merge比传统的先判断再选择插入或更新快很多

Global site tag (gtag.js) - Google Analytics