Oracle 数据库实现数据合并：merge

ゝ一世哀愁。 2021-12-15 14:27 603阅读 0赞

1、使用update进行数据更新

1）最简单的更新

update tablea a set a.price=1.00

2）带条件的数据更新

update tablea a set a.price = 2.00 where a.id='02'

3）两张表关联更新为固定值

update tablea a set a.price =3.00 where exits(select 1 from tableb b where a.id=b.id)

将a,b相同id的 a表的price 字段更新为 3.00

4）关联更新数据来源第二张表

update tablea a set a.price=(select price from tablec c ) where exits (select 1 from tablec c where a.id=c.id)

将a表price字段 更新为 id和c表id相同的数据

5）关联更新多个字段

update tablea a set ( a.price,a.type)=(select c.price,c.type from tablec c ) where exits (select 1 from tablec c where a.id=c.id)

更新a表的price 和 type 字段

6）使用视图方式更新

update (select a.price old,c.price as new from tablea a ,tablec c where a.id=c.id) set old=new

以上为自己了解到的Update使用方式，需要注意 a.id 和c.id需要一一对应。即c表只有一条id 与a表id对应，否则会报错

### [ORA-01427:][ORA-01427]"single-row subquery returns more than one row" ###

单行查询返回多行结果。是不能进行更新的。

2、merge 更新使用

工作中要对一个字段：次数 进行更新 表数据量在 13w+ 需要两表关联 也就是 两个 13w+ 的表进行关联。

在使用update进行更新的时候，效率问题大大降低。加上限制条件更新 100条数据还用了6-8S，所以 update并不适用。

查阅资料看到merge 更新，便学习记录。

MERGE 在[SQL Server][]、[Oracle][]数据库中可用，MySQL、[PostgreSQL][]中不可用。可以同时进行更新和插入操作。执行效率要高于INSERT+UPDATE。

语法：

MERGE INTO \[your table-name\] \[rename your table here\]  
　　USING ( \[ your query \] )\[rename your query-sql and using just like a table\]  
　　ON (\[conditional expression \] AND \[...\]...)  
　　WHEN MATHED THEN \[here you can execute some update sql or something else \]  
　　WHEN NOT MATHED THEN \[execute something else here ! \]

示例

merge into tablea a ----要更新或者操作的表

using tablec c 　　　　----源表 using (select \* from tablec ) c

on a.id=c.id　　　　　　--匹配条件

when matched then set a.price=c.price --当匹配时进行更新操作

when not matched then 　　　　--不匹配进行插入操作

insert values values(c.id,c.price)

using 后不仅可以使用 表 也可以是 视图或者子查询 using (select \* from tablec ) c

not matched 可以没有 就是当不匹配什么也不做。

总结：

之前说的使用update更新100行数据都需要6-8S 使用merge 更新全部数据（13W+ 与13W+ 关联）只用了10S左右。更新效率可见要比update高很多。

SQL Server 2008 开始支持 MERGE语句 
     
    -- 源表
    CREATE TABLE test_from (id INT, val VARCHAR(20));
     
    -- 目标表
    CREATE TABLE test_to (id INT, val VARCHAR(20));
     
    -- 插入源表
    INSERT INTO test_from VALUES (1, 'A');
    INSERT INTO test_from VALUES (2, 'B');
     
    -- 合并 源表到目标表
    MERGE test_to USING test_from
    ON ( test_to.id = test_from.id )    -- 条件是 id 相同
    WHEN MATCHED THEN UPDATE SET test_to.val = test_from.val   -- 匹配的时候，更新
    WHEN NOT MATCHED THEN INSERT VALUES(test_from.id, test_from.val) -- 源表有，目标表没有，插入
    WHEN NOT MATCHED BY SOURCE THEN DELETE; -- 目标表有，源表没有，目标表该数据删除.
     
    -- 第一次检查 目标表数据.
    SELECT * FROM test_to;
     
    id          val
    ----------- --------------------
              1 A
              2 B
     
     
    -- 更新源表
    UPDATE test_from SET val = 'A2' WHERE id = 1;
    -- 删除源表
    DELETE FROM test_from WHERE id = 2;
    -- 插入源表
    INSERT INTO test_from VALUES (3, 'C');
     
     
    -- 合并 源表到目标表
    MERGE test_to USING test_from
    ON ( test_to.id = test_from.id )    -- 条件是 id 相同
    WHEN MATCHED THEN UPDATE SET test_to.val = test_from.val   -- 匹配的时候，更新
    WHEN NOT MATCHED THEN INSERT VALUES(test_from.id, test_from.val) -- 源表有，目标表没有，插入
    WHEN NOT MATCHED BY SOURCE THEN DELETE; -- 目标表有，源表没有，目标表该数据删除.
     
    -- 再次检查 目标表数据.
    SELECT * FROM test_to;
     
    id          val
    ----------- --------------------
              1 A2
              3 C

Merge-数据库风格的合并  
数据的合并（merge）和连接（join）是我们在数据分析和挖掘中不可或缺的，是通过一个或一个以上的键连接的。pandas的合并（merge）的的绝大功能和数据库操作类似的。具有如下参数：

pd.merge(left, right, how=’inner’, on=None, left\_on=None, right\_on=None, left\_index=False,right\_index=False, sort=False, suffixes=(‘\_x’, ‘\_y’), copy=True, indicator=False, validate=None)

各个参数的含义：  
left、right：左右连接对象

how：连接方式，共有’inner’,’left’,right’,’outer’

on：根据连接的键

left\_on、right\_on：在连接的键名不同的情况下使用，left\_on传入左对象的键，right\_on传入右对象的键

left\_index、right\_index：设置索引是否作为连接的键，通常 left\_on=??和right\_index=True, right\_on=??和left\_index=True，或者left\_index=True和right\_index=True

sort：对连接后的结果是否排序，当数据巨大的时候为了追求效率可以设置为False

suffixes：对于不作为键的同名列，在其列名后添加的后缀

copy：将左右对象的内容复制过来，默认为True  
\---------------------

MERGE语句是SQL语句的一种。在SQL Server、Oracle数据库中可用，MySQL、PostgreSQL中不可用。MERGE是Oracle9i新增的语法，用来合并

UPDATE和INSERT语句。通过MERGE语句，根据一张表（原数据表，source table）或子查询的连接条件对另外一张（目标表，target table）表

进行查询，连接条件匹配上的进行UPDATE，无法匹配的执行INSERT。这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERT

\+UPDATE.具体来说，MERGE语句会检查原数据表记录和目标表记录。如果记录在原数据表和目标表中均存在，则目标表中的记录将被原数据表中的

记录所更新（执行Update操作）；如果目标表中不存在的某（些）记录，在原数据表中存在，则原数据表的这（些）记录将被插入到目标表中（

执行Insert操作）。在Oracle 10g之前，merge语句支持匹配更新和不匹配插入两种简单的用法，在10g中Oracle对merge语句做了增强，增加了

条件选项和DELETE操作。

### Merge语法 ###

**MERGE \[hint\] INTO \[schema \] table \[t\_alias\]**

**USING \[schema \]** **\{ table | view | subquery \} \[t\_alias\]**

**ON ( condition )**

**WHEN MATCHED THEN merge\_update\_clause**

**WHEN NOT MATCHED THEN merge\_insert\_clause;**

### 代码及说明 ###

1.MERGE INTO test T1

2.USING (SELECT OWNER, OBJECT\_NAME, MAX(ID) ID FROM T GROUP BY OWNER, OBJECT\_NAME) T

3.ON (T,OWNER = T1,OWNER AND T.OBJECT\_NAME = T1,TABLE\_NAME)

4.WHEN MATCHED THEN UPDATE SET T1,ID = T,ID

5.WHEN NOT MATCHED THEN INSERT VALUES (T,ID, T,OWNER, T,OBJECT\_NAME);

第一行 命名目标表并给别名T1

第二行 using子句提供merge操作的数据源，命名T

第三行 on子句指定合并的条件

第四行 when matched then 子句判定条件符合则对表T1做什么改变（或删除）

第五行 when not matched then 子句判断条件不符合则插入的操作

上面为数据库中merge，下面为hibernate中merge和update的区别

1. 数据库记录已存在，更改person的name为一个新的name。  
  
merge方法打印出的日志如下:  
Hibernate: select person0\_.id as id0\_0\_, person0\_.name as name0\_0\_ from person person0\_ where person0\_.id=?  
Hibernate: update person set name=? where id=?  
  
update方法打印出的日志如下:  
Hibernate: update person set name=? where id=?  
  
2. 数据库记录已存在，更改person的name和数据库里对应id记录的name一样的值。  
  
merge方法打印出的日志如下:  
Hibernate: select person0\_.id as id0\_0\_, person0\_.name as name0\_0\_ from person person0\_ where person0\_.id=?  
此处相对于第一种情形少了update的动作  
  
update方法打印出的日志如下:  
Hibernate: update person set name=? where id=?  
  
3. 数据库记录不存在时，也就是你传的实体bean的ID在数据库没有对应的记录。  
  
merge方法打印出的日志如下:  
Hibernate: select person0\_.id as id0\_0\_, person0\_.name as name0\_0\_ from person person0\_ where person0\_.id=?  
Hibernate: insert into person (name) values (?)  
如果没有对应的记录，merge会把该记录当作新的记录来插入。此处我很疑惑，因为我传得person实体对象里写明了id值的，它为什么还会做插入的动作呢？

转载于:https://www.cnblogs.com/klb561/p/10660390.html

[ORA-01427]: http://www.baidu.com/link?url=rgqxUDuOsmF9nt1kJ2kNfW60LMgeW6WmHRvocEG3t69q9e8K-yVE5xJjssm799VD
[SQL Server]: https://baike.baidu.com/item/SQL%20Server
[Oracle]: https://baike.baidu.com/item/Oracle
[PostgreSQL]: https://baike.baidu.com/item/PostgreSQL