精华内容
下载资源
问答
  • Mybatis批量foreach merge into的用法,这是介绍Mybatis批量foreach merge into的用法的文档
  • 非常经典的SQL经验,适合于数据库初学者及长期从事软件开发者
  • 主要介绍了oracle中merge into用法及实例解析,小编觉得挺不错的,现在分享给大家,也给大家做个参考。一起跟随小编过来看看吧
  • oracle数据匹配merge into的实例详解 前言:  很久之前,估计在2010年左右在使用Oralce,当时有个需求就是需要对两个表的数据进行匹配,这两个表的数据结构一致,一个是正式表,一个是临时表,这两表数据量还算是...
  • DB2中的Merge语句可以将一个表中的数据合并到另一个表中,在合并的同时可以根据条件进行插入、删除、更新等操作,功能非常强大。
  • 使用merge into进行大数据量优化

    千次阅读 2020-03-09 00:21:44
    insert into tableA select xx from tableB 或者使用 <foreach collection="pd.mapListImpt" item="item" index="index" open="begin" close=";end;" separator=";" > update tableA <set> a=.....

    传统方式:

    insert into tableA select xx from tableB
    

    或者使用

    <foreach collection="pd.mapListImpt" item="item" index="index" open="begin" close=";end;" separator=";" >
    update tableA
    			<set>
    				a= #{pd.mapListImpt[${index}].a,jdbcType=VARCHAR},
    				b= #{pd.mapListImpt[${index}].b,jdbcType=VARCHAR},
    
    				c=#{pd.mapListImpt[${index}].c,jdbcType=VARCHAR},
    				d= #{pd.mapListImpt[${index}].d,jdbcType=VARCHAR},
    
    				e=  #{pd.mapListImpt[${index}].e,jdbcType=VARCHAR},
    
    				f=  #{pd.mapListImpt[${index}].f,jdbcType=VARCHAR},
    				g=  #{pd.mapListImpt[${index}].g,jdbcType=VARCHAR},
    
    
    				h=#{pd.h,jdbcType=VARCHAR},
    				UpdateOperater=#{pd.userCode,jdbcType=VARCHAR},
    				UpdateDate=to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd'),
    				UpdateTime=to_char(sysdate, 'HH24:mi:ss')
    			</set>
    			where ad=#{pd.mapListImpt[${index}].ad,jdbcType=VARCHAR}
    </foreach>
    

    优化方式

    使用merge into
    以下写的例子,以供以后参考

    <insert id="insertCEBPN_Con_ContriLs" statementType="PREPARED" parameterType="pd">
    		merge into PN_Con_ContriLs pnc
        using(
           select
             pcc.contrilsid contrilsid,
             pcc.contributionid contributionid,
    
             pcc.PlanID PlanID,
             pcc.EnterprisedeID EnterprisedeID,
             pcc.SubEnterprisedeID SubEnterprisedeID,
             pcc.productid productid,
             pcc.StaffID StaffID,
             pcc.name name,
             pcc.IdNo IdNo,
             nvl(pcc.EContTaxApply,'0') + pcc(pnc.ECMoney,'0') ECTotal,
             nvl(pcc.SContTaxApply,'0') + pcc(pnc.SCMoney,'0') SCTotal,
             pcc.CSumMoney CSumMoney,
             pcc.EContTaxApply EContTaxApply,
    			   pcc.SContTaxApply SContTaxApply,
    			   pcc.ECMoney ECMoney,
    			   pcc.SCMoney SCMoney,
             pcc.expandfield2 expandfield2
          from pn_con_contrils_temp pcc
          where pcc.contributionid = #{pd.ContributionID,jdbcType=VARCHAR}
    				    and pcc.expandfield4 = #{pd.ContriLsIDLSPay,jdbcType=VARCHAR}
        )t on(pnc.contributionid=t.contributionid)
        when not matched then
        insert (pnc.ContriLsID,pnc.ContributionID,pnc.planid,pnc.EnterprisedeID,pnc.SubEnterprisedeID,pnc.ProductID,pnc.StaffID,pnc.name,  pnc.IdNo,
    		   pnc.EContTotalLs,pnc.SContTotalLs, pnc.ContTotalLs,pnc.EContTaxTotalLs,pnc.SContTaxTotalLs,pnc.EContApply,pnc.SContApply,
    		   pnc.OperateOrg, pnc.Operater,pnc.MakeDate, pnc.MakeTime,pnc.UpdateOperater,pnc.UpdateDate,pnc.UpdateTime,pnc.ContriListFileID,pnc.ContriAccountType)
        values(
             t.contrilsid,
             t.contributionid,
             t.PlanID,
             t.EnterprisedeID,
             t.SubEnterprisedeID,
             t.productid,
             t.StaffID,
             t.name,
             t.IdNo,
             t.ECTotal,
             t.SCTotal,
             t.CSumMoney,
             t.EContTaxApply,
             t.SContTaxApply,
             t.ECMoney,
             t.SCMoney,
             #{pd.comCode, jdbcType = VARCHAR},
             #{pd.userCode, jdbcType = VARCHAR},
             to_date(to_char(sysdate, 'yyyy-MM-dd'), 'yyyy-MM-dd'),
             to_char(sysdate, 'HH24:mi:ss'),
             #{pd.userCode, jdbcType = VARCHAR},
             to_date(to_char(sysdate, 'yyyy-MM-dd'), 'yyyy-MM-dd'),
             to_char(sysdate, 'HH24:mi:ss'),
             #{pd.FileID, jdbcType = VARCHAR},
             t.expandfield2
          )
    	</insert>
    
    <insert id="updateCEBPN_Con_ContriLs" statementType="PREPARED" parameterType="pd">
    		merge into PN_Con_ContriLs pnc
    using(
           select
               pcc.contrilsid ContriLsID,
               pcc.contributionid ContributionID,
               pcc.name Name,
               pcc.idno IDNo,
               pcc.EContTaxApply,
               pcc.ECMoney,
               (nvl(pcc.EContTaxApply,'0')+nvl(pcc.ECMoney,'0')) EContTotalLs,
               pcc.SContTaxApply,
               pcc.SCMoney,
               (nvl(pcc.SContTaxApply,'0')+nvl(pcc.SCMoney,'0')) SContTotalLs,
               pcc.CSumMoney
        from pn_con_contrils_temp pcc
        where pcc.contributionid = #{pd.ContributionID,jdbcType=VARCHAR}
          	  and pcc.expandfield4 = #{pd.ContriLsIDLSPay,jdbcType=VARCHAR}
          )t  on(t.contributionid = pnc.contributionid and pnc.Name = t.Name and pnc.IDNo = t.IDNo)
    WHEN MATCHED THEN
    			UPDATE  SET
               		   pnc.EContTotalLs = t.EContTotalLs,
    				   pnc.SContTotalLs = t.SContTotalLs,
    				   pnc.EContTaxTotalLs = t.EContTaxApply,
    				   pnc.SContTaxTotalLs = t.SContTaxApply,
    				   pnc.ContTotalLs =  t.CSumMoney,
    				   pnc.EContApply=  t.ECMoney,
    				   pnc.SContApply=  t.SCMoney,
    
    				   pnc.ContriListFileID=#{pd.FileID,jdbcType=VARCHAR},
    				   pnc.UpdateOperater=#{pd.userCode,jdbcType=VARCHAR},
    				   pnc.UpdateDate=to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd'),
    				   pnc.UpdateTime=to_char(sysdate, 'HH24:mi:ss')
    	</insert>
    

    下面是参考的其他博主的记录,以供日后学习
    转载自:https://blog.csdn.net/jeryjeryjery/article/details/70047022


    merge into的形式:

    MERGE INTO [target-table] A USING [source-table sql] B ON([conditional expression] and [...]...)
    WHEN MATCHED THEN
    	[UPDATE sql]
    WHEN NOT MATCHED THEN
    	[INSERT sql]
    

    作用:判断B表和A表是否满足ON中条件,如果满足则用B表去更新A表,如果不满足,则将B表数据插入A表但是有很多可选项,如下:

    1.正常模式

    2.只update或者只insert

    3.带条件的update或带条件的insert

    4.全插入insert实现

    5.带delete的update(觉得可以用3来实现)
    下面一一测试。

    测试建以下表:

    
     
    1. create table A_MERGE
    2. (
    3. id NUMBER not null,
    4. name VARCHAR2( 12) not null,
    5. year NUMBER
    6. );
    7. create table B_MERGE
    8. (
    9. id NUMBER not null,
    10. aid NUMBER not null,
    11. name VARCHAR2( 12) not null,
    12. year NUMBER,
    13. city VARCHAR2( 12)
    14. );
    15. create table C_MERGE
    16. (
    17. id NUMBER not null,
    18. name VARCHAR2( 12) not null,
    19. city VARCHAR2( 12) not null
    20. );
    21. commit;
    其表结构截图如下图所示:

    A_MERGE表结构:


    B_MERGE表结构


    C_MERGE表结构


    1.正常模式

    先向A_MERGE和B_MERGE插入测试数据:

    
     
    1. insert into A_MERGE values( 1, 'liuwei', 20);
    2. insert into A_MERGE values( 2, 'zhangbin', 21);
    3. insert into A_MERGE values( 3, 'fuguo', 20);
    4. commit;
    5. insert into B_MERGE values( 1, 2, 'zhangbin', 30, '吉林');
    6. insert into B_MERGE values( 2, 4, 'yihe', 33, '黑龙江');
    7. insert into B_MERGE values( 3, 3, 'fuguo',, '山东');
    8. commit;

    此时A_MERGE和B_MERGE表中数据截图如下:

    A_MERGE表数据:


    B_MERGE表数据:


    然后再使用merge into用B_MERGE来更新A_MERGE中的数据:

    
     
    1. MERGE INTO A_MERGE A USING ( select B.AID,B.NAME,B.YEAR from B_MERGE B) C ON (A.id=C.AID)
    2. WHEN MATCHED THEN
    3. UPDATE SET A.YEAR=C.YEAR
    4. WHEN NOT MATCHED THEN
    5. INSERT(A.ID,A.NAME,A.YEAR) VALUES(C.AID,C.NAME,C.YEAR);
    6. commit;
    此时A_MERGE中的表数据截图如下:


    2.只update模式
    首先向B_MERGE中插入两个数据,来为了体现出只update没有insert,必须有一个数据是A中已经存在的

    另一个数据时A中不存在的,插入数据语句如下:

    
     
    1. insert into B_MERGE values( 4, 1, 'liuwei', 80, '江西');
    2. insert into B_MERGE values( 5, 5, 'tiantian', 23, '河南');
    3. commit;
    此时A_MERGE和B_MERGE表数据截图如下:
    A_MERGE表数据截图:


    B_MERGE表数据截图:


    然后再次用B_MERGE来更新A_MERGE,但是仅仅update,没有写insert部分。

    
     
    1. merge into A_MERGE A USING ( select B.AID,B.NAME,B.YEAR from B_MERGE B) C ON(A.ID=C.AID)
    2. WHEN MATCHED THEN
    3. UPDATE SET A.YEAR=C.YEAR;
    4. commit;
    merge完之后A_MERGE表数据截图如下:可以发现仅仅更新了AID=1的年龄,没有插入AID=4的数据


    3.只insert模式
    首先改变B_MERGE中的一个数据,因为上次测试update时新增的数据没有插入到A_MERGE,这次可以用。

    
     
    1. update B_MERGE set year= 70 where AID= 2;
    2. commit;
    此时A_MERGE和B_MERGE的表数据截图如下:
    A_MERGE表数据:


    B_MERGE表数据:


    然后用B_MERGE来更新A_MERGE中的数据,此时只写了insert,没有写update:

    
     
    1. merge into A_MERGE A USING ( select B.AID,B.NAME,B.YEAR from B_MERGE B) C ON(A.ID=C.AID)
    2. WHEN NOT MATCHED THEN
    3. insert(A.ID,A.NAME,A.YEAR) VALUES(C.AID,C.NAME,C.YEAR);
    4. commit;
    此时A_MERGE的表数据截图如下:



    4.带where条件的insert和update。
    我们在on中进行完条件匹配之后,还可以在后面的insert和update中对on筛选出来的记录再做一次条件判断,用来控制哪些要更新,哪些要插入。
    测试数据的sql代码如下,我们在B_MERGE修改了两个人名,并且增加了两个人员信息,但是他们来自的省份不同,
    所以我们可以通过添加省份条件来控制哪些能修改,哪些能插入:

    
     
    1. update B_MERGE set name= 'yihe++' where id= 2;
    2. update B_MERGE set name= 'liuwei++' where id= 4;
    3. insert into B_MERGE values( 6, 6, 'ningqin', 23, '江西');
    4. insert into B_MERGE values( 7, 7, 'bing', 24, '吉安');
    5. commit;
    A_MGERGE表数据截图如下:


    B_MERGE表数据:


    然后再用B_MERGE去更新A_MERGE,但是分别在insert和update后面添加了条件限制,控制数据的更新和插入:

    
     
    1. merge into A_MERGE A USING ( select B.AID,B.name,B.year,B.city from B_MERGE B) C
    2. ON(A.id=C.AID)
    3. when matched then
    4. update SET A.name=C.name where C.city != '江西'
    5. when not matched then
    6. insert(A.ID,A.name,A.year) values(c.AID,C.name,C.year) where C.city= '江西';
    7. commit;


    此时A_MERGE截图如下:



    5.无条件的insert。
    有时我们需要将一张表中所有的数据插入到另外一张表,此时就可以添加常量过滤谓词来实现,让其只满足
    匹配和不匹配,这样就只有update或者只有insert。这里我们要无条件全插入,则只需将on中条件设置为永假
    即可。用B_MERGE来更新C_MERGE代码如下:

    
     
    1. merge into C_MERGE C USING ( select B.AID,B.NAME,B.City from B_MERGE B) C ON ( 1= 0)
    2. when not matched then
    3. insert(C.ID,C.NAME,C.City) values(B.AID,B.NAME,B.City);
    4. commit;
    C_MERGE表在merge之前的数据截图如下:


    B_MERGE数据截图如下:


    C_MERGE表在merge之后数据截图如下:



    6.带delete的update
       MERGE提供了在执行数据操作时清除行的选项. 你能够在WHEN MATCHED THEN UPDATE子句中包含DELETE子句. 
    DELETE子句必须有一个WHERE条件来删除匹配某些条件的行.匹配DELETE WHERE条件但不匹配ON条件的行不会被从表中删除.
    但我觉得这个带where条件的update差不多,都是控制update,完全可以用带where条件的update来实现。







      author:su1573

      展开全文
    • SQL语句merge into的用法

      千次阅读 2020-07-24 13:56:07
      改用 MERGE INTO 代替执行批量更新,会提升执行效率。 merge into将源数据(来源于实际的表,视图,子查询)更新或插入到指定的表中(必须实际存在),好处是避免了多个insert 和update操作。 merge into是一个目标性明确的...

      应用场景

      在批量更新大量的数据时,使用Insert 和Update操作会出现效率低下,甚至卡死的情况。改用 MERGE INTO 代替执行批量更新,会提升执行效率。

      merge into将源数据(来源于实际的表,视图,子查询)更新或插入到指定的表中(必须实际存在),好处是避免了多个insert 和update操作。

      merge into是一个目标性明确的操作符,不允许在一个merge 语句中对相同的行insert或update操作。

      这个语法仅需要一次全表扫描就完成了全部工作,执行效率要高于insert+update,尤其是在大数据量面前,效率越明显。

      语法

      语法如下:

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

      其中,table_name 指的是更新的表,using()里边的指的是数据来源表/视图/子查询结果集,condition指的是连接条件。
      如果满足连接条件,则更新字段;如果连接条件不满足,则停止更新进行插入。

      举例

      table_a : 计划金额表

      table_b : 调整表

      table_c : 调整金额表

      想要根据调整id,将调整金额累加到计划金额表中。一次更新当年12个月。

      MERGE INTO table_a t1 USING (
      	SELECT
      		m.plan_id,
      		m.adjust_money + d.adjust_money adjust_money,
      		m.all_money + d.adjust_money all_money
      	FROM
      		table_a m
      	LEFT JOIN table_b a ON m.card_id = a.card_id
      	LEFT JOIN table_c d ON a.adjust_id = d.adjust_id
      	WHERE
      		a.adjust_id = 5
      	AND a.comp_code = '100101'
      	AND m.year = d.year
      ) t2 ON (t1.plan_id = t2.plan_id)
      WHEN matched THEN
      	UPDATE
      SET t1.adjust_money = t2.adjust_money,
       t1.all_money = t2.all_money
      
      展开全文
    • merge into 的用法

      千次阅读 2020-10-23 11:41:12
      merge into 的语法如下所示: MERGE INTO [target-table] T USING [source-table sql] S ON([conditional expression] and [...]...) WHEN MATCHED THEN [UPDATE sql] WHEN NOT MATCHED THEN [INSERT sql] 判断源表...

      1. 语法

      merge into 的语法如下所示:

      MERGE INTO [target-table] T USING [source-table sql] S ON([conditional expression] and [...]...)
      WHEN MATCHED
      THEN [UPDATE sql]
      WHEN NOT MATCHED
      THEN [INSERT sql]
      

      判断源表 S 和目标表 T 是否满足 ON 中的条件,如果满足则用 S 表去更新 T 表,如果不满足,则将 S 表数据插入 T 表中。但是有很多可选项,如下:

      • 普通模式
      • 只 update 或者只 insert
      • 无条件 insert 实现
      • 带 delete 的 update

      2. 测试表

      -- 目标表
      create table target
      (
      id NUMBER not null,
      name VARCHAR2(12) not null,
      year NUMBER
      );
      
      -- 源表
      create table source
      (
      id NUMBER not null,
      aid NUMBER not null,
      name VARCHAR2(12) not null,
      year NUMBER,
      city VARCHAR2(12)
      );
      -- 插入测试数据
      insert into target values(1,'liuwei',20);
      insert into target values(2,'zhangbin',21);
      insert into target values(3,'fuguo',20); 
      
      insert into source values(1,2,'zhangbin',30,'吉林');
      insert into source values(2,4,'yihe',33,'黑龙江');
      insert into source values(3,3,'fuguo','','山东');
      

      两表的的查询结果如下所示:

      SQL> select * from target;
      
              ID NAME               YEAR
      ---------- ------------ ----------
               1 liuwei               20
               2 zhangbin             21
               3 fuguo                20
      
      SQL> select * from source;
      
              ID        AID NAME               YEAR CITY
      ---------- ---------- ------------ ---------- ------------
               1          2 zhangbin             30 吉林
               2          4 yihe                 33 黑龙江
               3          3 fuguo                   山东
      

      3. 普通模式

      现在简单的使用 merge into 来实现当符合 on 中的条件时则进行更新操作,否则进行插入操作的功能。查看 target 和 source 表,其中 target 表中的 id 和 source 和 aid 是关联的,现在我们要实现当 target 表的 id 与 source 的 aid 匹配时,使用 source 表的 year 去更新 target 表的 year,否则将 source 中的记录插入到 target 表中,具体实现 sql 如下所示:

      merge into target t using source s on (t.id = s.aid)
      when matched then
      update set t.year = s.year
      when not matched then
      insert values(s.aid, s.name, s.year);
      

      执行结果如下所示:

      SQL> select * from target;
      
              ID NAME               YEAR
      ---------- ------------ ----------
               1 liuwei               20 -- 原有记录
               2 zhangbin             30 -- 更新为 30
               3 fuguo                   -- 更新为 空
               4 yihe                 33 -- 新插入记录
      

      4. 只 update

      还原 target 表的数据,现在来实现当 target 表的 id 与 source 的 aid 匹配时,使用 source 表的 year 去更新 target 表的 year 的操作,具体实现 sql 如下所示:

      merge into target t using (select aid, name, year from source) s on (t.id = s.aid)
      when matched then
      update set t.year = s.year;
      

      执行结果如下所示:

      SQL> select * from target;
      
              ID NAME               YEAR
      ---------- ------------ ----------
               1 liuwei               20 -- 原有记录
               2 zhangbin             30 -- 更新为 30
               3 fuguo            	   -- 更新为 空
      

      还原 target 表的数据,我们也可以在 update 子句添加限定条件,比如使用 source 表的 year 去更新 target 表的 year 的操作时,限定 source 表的记录除了与 target 是匹配的条件之外,city 必须是吉林的,具体实现 sql 如下:

      merge into target t using source s on (t.id = s.aid)
      when matched then
      update set t.year = s.year where s.city = '吉林';
      

      执行结果如下所示:

      SQL> select * from target;
      
              ID NAME               YEAR
      ---------- ------------ ----------
               1 liuwei               20 -- 原有记录
               2 zhangbin             30 -- 更新为 30
               3 fuguo                20 -- 原有记录
      
      

      5. 只 insert

      还原 target 表的数据,现在来实现当 target 表的 id 与 source 的 aid 不匹配时,将 source 中的记录插入到 target 表中,具体实现 sql 如下:

      merge into target t using source s on (t.id = s.aid)
      when not matched then
      insert(t.id, t.name, t.year) values(s.aid, s.name, s.year);
      
      

      执行结果如下所示:

      SQL> select * from target;
      
              ID NAME               YEAR
      ---------- ------------ ----------
               1 liuwei               20 -- 原有记录
               2 zhangbin             21 -- 原有记录
               3 fuguo                20 -- 原有记录
               4 yihe                 33 -- 新增记录
      
      

      insert 子句也可以添加限定条件,类似 update,这里不再赘述。

      6. 无条件 insert

      还原 target 表的数据。有时我们需要将一张表中所有的数据插入到另外一张表,此时就可以添加常量过滤谓词来实现,让其只满足匹配和不匹配,这样就只有update或者只有insert。这里我们要无条件全插入,则只需将on中条件设置为永假即可。用 source 表来更新 target 代码如下:

      merge into target t using source s on(1 = 0) -- 设置永假匹配条件
      when not matched then
      insert(t.id, t.name, t.year) values(s.aid, s.name, s.year);
      
      

      执行结果如下所示:

      SQL> select * from target;
      
              ID NAME               YEAR
      ---------- ------------ ----------
               1 liuwei               20
               2 zhangbin             21
               3 fuguo                20
               2 zhangbin             30 -- 新增记录
               4 yihe                 33 -- 新增记录
               3 fuguo        		   -- 新增记录
      
      

      7. 带 delete 的 update

      还原 target 表的数据。删除 target 表中与 source 表匹配的符合指定条件的记录。需要注意的是,delete 子句附属于 update 子句,也就是要有 delete 子句必须有 update 子句。如下语句使用 source 表的记录来匹配更新 target 表中的记录。同时删除匹配的记录中 target 表中 id 为2 的记录,具体实现 sql 如下所示:

      merge into target t using source s on(t.id = s.aid)
      when matched then update set t.year = s.year
      delete where(t.id = 2);
      
      

      执行结果如下所示:

      -- id 为 2 的记录被删除
      SQL> select * from target;
      
              ID NAME               YEAR
      ---------- ------------ ----------
               1 liuwei               20 -- 原有记录
               3 fuguo                   -- year 被更新
      
      
      展开全文
    • 2)merge into实现 MERGE INTO t1 USING t2 ON t1.id-t2.id WHEN MATCHED Then UpDate set t1.sale=t2.sale When Not Matched When Not Matched then insert values(t2.id,t2.name,t2.sale,sysdate); --Merge的最后...

      概述

      多表关联查询的时候会用到临时表插入数据,然后再用select查行查询,在往临时表里插入数据的时候,我们经常会用到判断如果临时表里有了这部分数据我们就要更新数据,如果临时表里没有这部分数据我们就要插入,这个时候可以怎么去实现呢?
      下面介绍各类型数据库实现的大致方式。

      一、存储过程实现

      各类型数据库都可以通过存储过程实现,因为是共性问题,所以就放前面了,这里以mysql数据库的存储过程为例。

      1、环境准备

      --建表
      create  table t1(
      id  bigint(10),
      name varchar(16),
      sale bigint(10),
      operatime datetime);
      create table t2(
      id  bigint(10),
      name varchar(16),
      sale bigint(20));
      
      -- 插入数据
      INSERT into t1 values(1,"xiaohong",1000,now());
      INSERT into t1 values(2,"xiaoming",500,now());
      INSERT into t2 values(1,"xiaohong",300);
      INSERT into t2 values(2,"xiaoming",400);
      INSERT into t2 values(3,"xiaoxiao",900);
      

      在这里插入图片描述

      2、存储过程实现

      delimiter $
      CREATE PROCEDURE merge_t2_to_t1 () BEGIN
      -- 定义需要插入从a表插入b表的过程变量
      DECLARE _ID bigint(10);
      DECLARE _NAME VARCHAR (16);
      DECLARE _SALE VARCHAR (16);
      
      -- 游标遍历数据结束标志 
      DECLARE done INT DEFAULT FALSE;
      -- 游标指向a表结果集第一条-1位置
      DECLARE cur_account CURSOR FOR SELECT ID, NAME,SALE FROM t2;
      -- 游标指向a表结果集最后一条加1位置 设置结束标志
      DECLARE CONTINUE HANDLER FOR NOT FOUND  SET done = TRUE;
      -- 打开游标
      OPEN cur_account;
      -- 遍历游标
      read_loop :
      LOOP
      --  取值a表当前位置数据到临时变量
          FETCH NEXT FROM cur_account INTO _ID,_NAME,_SALE;
       
      -- 如果取值结束 跳出循环
      IF done THEN LEAVE read_loop; 
      END IF;
       
      -- 当前数据做对比,如果b表存在则更新时间 不存在则插入
      IF NOT EXISTS ( SELECT 1 FROM t1 WHERE ID = _ID AND NAME=_NAME ) 
          THEN
              INSERT INTO t1 (ID, NAME,sale,operatime) VALUES (_ID,_NAME,_sale,now());
          ELSE 
          UPDATE t1  set sale = _sale WHERE ID = _ID AND NAME=_NAME;
      END IF;
       
      END LOOP;
      CLOSE cur_account;
       
      END $
      

      3、调用存储过程

      call merge_t2_to_t1();
      

      在这里插入图片描述

      二、sqlserver的merge into语法

      在SQL Server 2008的时候微软增加了一个强大的语句Merge。

      1、语法

      MERGE 语句是首先对源表和目标表都进行完全表扫描,然后拿源表和目标表检查,匹配条件,若成立则执行SQL语句1,不成立则执行SQL语句2,最执行SQL语句3。
      语法:

      MERGE 
          [ TOP ( expression ) [ PERCENT ] ] 
          [ INTO ] <操作表> --即将做插入、更新、删除的表
          USING <源表或者数据集或者子查询> --用户提供匹配条件来源的集合或者表
          ON <匹配条件> --可以是任意有效的条件组合
          [ WHEN MATCHED [ AND <clause_search_condition> ]--匹配条件成立
              THEN <SQL语句1> ]
          [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]--匹配条件不成立
              THEN <SQL语句2> ]
          [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]--目标变不存在而源表存在的数据
              THEN <SQL语句3> ] 
      ;  -- 不要忘记分号
      

      注意:
      1)Merge操作的只是“操作表”,源表不会有任何变化
      2)Merge的最后结尾必须是以分号结束的,不能忘了分号
      3)语法严格要求关键字之间只能有一个英文空格,不能有多余的空格
      4)不一定要把三个操作都写全,可以根据实际情况

      2、实验

      1)环境准备

      --建表
      create  table t1(
      id   int,
      name VARCHAR(16),
      sale int,
      Operatime date);
      create  table t2(
      id   int,
      name VARCHAR(16),
      sale int);
      
      -- 插入数据
      INSERT into t1 values(1,'xiaohong',1000,sysdate);
      INSERT into t1 values(2,'xiaoming',500,sysdate);
      INSERT into t2 values(1,'xiaohong',300);
      INSERT into t2 values(2,'xiaoming',400);
      INSERT into t2 values(3,'xiaoxiao',900);
      commit;
      

      在这里插入图片描述
      2)merge into实现

      MERGE INTO t1  USING  t2
      ON t1.id-t2.id
      WHEN MATCHED Then UpDate set t1.sale=t2.sale
      When Not Matched 
      When Not Matched then insert values(t2.id,t2.name,t2.sale,sysdate);
      --Merge的最后结尾必须是以分号结束的,不能忘了分号 谨记:语法严格要求关键字之间只能有一个英文空格,不能有多余的空格
      

      在这里插入图片描述

      三、Oracle的merge into语法

      1、语法

      merge语法是根据源表对目标表进行匹配查询,匹配成功时更新,不成功时插入。

      MERGE INTO [target-table] A USING [source-table sql] B ON([conditional expression] and [...]...)
      WHEN MATCHED THEN
      [UPDATE sql]
      WHEN NOT MATCHED THEN
      [INSERT sql]
      

      2、实验

      1)环境准备

      --建表
      create  table t1(
      id   number,
      name VARCHAR(16),
      sale number,
      Operatime date);
      create  table t2(
      id   number,
      name VARCHAR(16),
      sale number);
      
      -- 插入数据
      INSERT into t1 values(1,'xiaohong',1000,sysdate);
      INSERT into t1 values(2,'xiaoming',500,sysdate);
      INSERT into t2 values(1,'xiaohong',300);
      INSERT into t2 values(2,'xiaoming',400);
      INSERT into t2 values(3,'xiaoxiao',900);
      commit;
      

      在这里插入图片描述
      2)Oracle merge into实现

      merge into t1 using t2 on (t1.id=t2.id) WHEN MATCHED THEN
      update set t1.sale=t2.sale
      WHEN NOT MATCHED THEN
      insert values(t2.id,t2.name,t2.sale,sysdate);
      

      在这里插入图片描述

      四、mysql的on duplicate key update语法

      mysql并没有oracle、mssql的merge into语法,但是有个on duplicate key update语法(不是标准的sql语法)可以实现merge into语法。
      ON DUPLICATE KEY UPDATE为Mysql特有语法,使用时应多注意主键和插入值是否是我们想要插入或修改的key、Value。
      创建表,注意要有一个唯一索引 new_code_index, 插入或者更新时,以此为标准。另外在高并发环境下,禁用insert into …on duplicate key update…,因为会造成mysql主从不一致。

      1、相关语法

      --单条记录下使用
      --假如t1表的主键或者UNIQUE 索引是a,如果数据库里面已经存在a=1的记录则更新这条记录的c字段的值为原来值+1,然后返回值为2。
      --如果不存在则插入a=1,b=2,c=3到数据库,然后返回值为1。
      INSERT INTO t1(a,b,c) VALUES (1,2,3)   ON DUPLICATE KEY UPDATE c=c+1;
      --多记录下使用
      INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6)  ON DUPLICATE KEY UPDATE c=VALUES(c);
      

      2、实验

      1)环境准备

      --建表
      create  table t1(
      id  bigint(10),
      name varchar(16),
      sale bigint(10),
      operatime datetime,
      UNIQUE KEY `idx_id` (`id`) USING BTREE
      );
      create table t2(
      id  bigint(10),
      name varchar(16),
      sale bigint(20),
      UNIQUE KEY `idx_id` (`id`) USING BTREE
      );
      
      -- 插入数据
      INSERT into t1 values(1,"xiaohong",1000,now());
      INSERT into t1 values(2,"xiaoming",500,now());
      INSERT into t2 values(1,"xiaohong",300);
      INSERT into t2 values(2,"xiaoming",400);
      INSERT into t2 values(3,"xiaoxiao",900);
      

      在这里插入图片描述
      2)on duplicate key update实现

      insert into t1 select id,name,sale,now() from t2 on duplicate key update sale=values(sale);
      

      在这里插入图片描述
      结果:实现了将表t2更新到表t1中去,存在就更新,不存在就插入,
      注意:id字段是主键或UNIQUE索引,不然只会插入t2表所有行数据

      五、mysql的replace into语法

      Mysql replace与replace into都是经常会用到的功能;replace其实是做了一次update操作,而不是先delete再insert;而replace into其实与insert into很像,但对于replace into,假如表中的一个旧记录与一个用于PRIMARY KEY或一个UNIQUE索引的新记录具有相同的值,则在新记录被插入之前,旧记录被删除。

      1、相关语法

      --前两种形式用的多些,对于那些没有给予值的列,MySQL将自动为这些列赋上默认值
      replace into table(col, ...) values(...)
      replace into table(col, ...) select ...
      replace into table set col=value, ...
      

      注意:
      1)replace根据UNIQUE约束的字段(设置为Primary Key),确定被替换的是哪一条记录。如果不存在要替换的记录, 那么就会插入一条新的记录。
      2)replace语句会删除原有的一条记录, 并且插入一条新的记录来替换原记录。
      3)一般用replace语句替换一条记录的所有列, 如果在replace语句中没有指定某列, 在replace之后这列的值被置空。
      4)replace语句不能根据where子句来定位要被替换的记录。
      5)常见update写法:update table set col=col+1 where id=1;
      使用replace into不支持这样的写法:replace into table set col=col+1,id=1;

      2、实验

      1)环境准备

      --建表
      create  table t1(
      id  bigint(10),
      name varchar(16),
      sale bigint(10),
      operatime datetime,
      UNIQUE KEY `idx_id` (`id`) USING BTREE
      );
      create table t2(
      id  bigint(10),
      name varchar(16),
      sale bigint(20),
      UNIQUE KEY `idx_id` (`id`) USING BTREE
      );
      
      -- 插入数据
      INSERT into t1 values(1,"xiaohong",1000,now());
      INSERT into t1 values(2,"xiaoming",500,now());
      INSERT into t2 values(1,"xiaohong",300);
      INSERT into t2 values(2,"xiaoming",400);
      INSERT into t2 values(3,"xiaoxiao",900);
      

      2)replace into实现

      replace into t1 select id,name,sale,now() from t2;
      

      在这里插入图片描述
      结果:实现了将表t2更新到表t1中去,存在就更新,不存在就插入。

      六、pg自定义函数实现

      oracle数据库中有merge函数,可在插入数据前判断:如果指定列数据不存在,则正常插入数据;如果指定列数据存在,则将此条数据更新为插入的数据。 postgresql数据库中没有类似自带函数,只能自己实现此功能
      以下方法只实现了oracle中merge函数的部分功能,而最大的问题是必须针对每个表创建自己的merge函数,比较适合在进行数据库迁移的时候配合外部表和触发器使用。

      1、环境准备

      --建表
      create  table t1(
      id  int PRIMARY KEY,
      name varchar(16),
      sale int,
      operatime timestamp with time zone);
      
      -- 插入数据
      INSERT into t1 values(1,'xiaohong',1000,now());
      INSERT into t1 values(2,'xiaoming',500,now());
      commit;
      

      在这里插入图片描述

      2、pg自定义函数实现

      该函数基于表级别。

      CREATE FUNCTION merge_db(id_new INT,name_new varchar(10),sale_new int) RETURNS VOID AS
      $$
      BEGIN
      LOOP
       UPDATE t1 SET sale = sale_new WHERE id = id_new and name = name_new;
       IF found THEN
             RETURN;
       END IF;
      BEGIN
       INSERT INTO t1 VALUES (id_new,name_new,sale_new,now());
       RETURN;
      EXCEPTION WHEN unique_violation THEN
      -- do nothing
      END;
      END LOOP;
      END;
      $$
      LANGUAGE plpgsql;
      

      在这里插入图片描述

      3、实现

      使用merge函数插入key列字段已经在表中存在的数据

      select merge_db(1,'xiaohong',400);
      select merge_db(2,'xiaoming',300);
      select merge_db(3,'xiaoxiao',700);
      select * from t1;
      

      在这里插入图片描述

      七、PG的WITH Queries语法

      PostgreSQL中不直接支持merge into这个语法,但PostgreSQL可以使用WITH Queries (Common Table Expressions)的方法实现相同的功能。

      1、语法

      主要是利用了postgresql的一个update特性—RETURNING,返回一个update的结果集,因为查询条件的存在(也因为它是主键,是唯一),就会将两张表重叠的部分给过滤出来,再用where not exists将这些重叠的部分给忽略掉,这样就将数据merge进去了。

      [WITH with_queries] SELECT select_list FROM table_expression [sort_specification]
      

      2、实验

      1)环境准备

      --建表
      create  table t1(
      id  int primary key,
      name text,
      sale int,
      operatime timestamp
      );
      create  table t2(
      id  int primary key,
      name text,
      sale int
      );
      
      -- 插入数据
      INSERT into t1 values(1,'xiaohong',1000,now());
      INSERT into t1 values(2,'xiaoming',500,now());
      INSERT into t2 values(1,'xiaohong',300);
      INSERT into t2 values(2,'xiaoming',400);
      INSERT into t2 values(3,'xiaoxiao',900);
      

      在这里插入图片描述
      2)with 实现
      用t2 这张表去更新t1 ,会将test1中没有的数据插入

      WITH upsert AS (
        UPDATE t1  SET sale = t2.sale  FROM t2  WHERE t1.id = t2.id  RETURNING t1.*
       )
      INSERT INTO t1 SELECT id,name,sale,now() FROM t2
      WHERE NOT EXISTS ( SELECT 1  FROM upsert b  WHERE t2.id = b.id);
      

      在这里插入图片描述

      八、pg的UPSERT语法

      PostgreSQL 9.5 引入了一项新功能,UPSERT(insert on conflict do),当插入遇到约束错误时,直接返回,或者改为执行UPDATE。

      1、语法

      [ WITH [ RECURSIVE ] with_query [, ...] ]  
      INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]  
          { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }  
          [ ON CONFLICT [ conflict_target ] conflict_action ]  
          [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]  
        
      where conflict_target can be one of:  
        
          ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]  
          ON CONSTRAINT constraint_name  
        
      and conflict_action is one of:  
        
          DO NOTHING  
          DO UPDATE SET { column_name = { expression | DEFAULT } |  
                          ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |  
                          ( column_name [, ...] ) = ( sub-SELECT )  
                        } [, ...]  
                    [ WHERE condition ] 
      

      2、实验

      1)环境准备

      --建表
      create  table t1(
      id  int primary key,
      name text,
      sale int,
      operatime timestamp
      );
      create  table t2(
      id  int primary key,
      name text,
      sale int
      );
      
      -- 插入数据
      INSERT into t1 values(1,'xiaohong',1000,now());
      INSERT into t1 values(2,'xiaoming',500,now());
      INSERT into t2 values(1,'xiaohong',300);
      INSERT into t2 values(2,'xiaoming',400);
      INSERT into t2 values(3,'xiaoxiao',900);
      

      在这里插入图片描述
      2)insert on implict实现

      --不存在则插入,存在则更新
      insert into t1 select id,name,sale,now() from t2 on conflict (id) do update set sale=excluded.sale;
      --不存在则插入,存在则直接返回(不做任何处理)
      insert into t1 select id,name,sale,now() from t2 on conflict (id) do nothing;
      

      在这里插入图片描述

      展开全文
    • Merge into的使用详解

      2016-07-13 14:27:35
      Merge into写法,含两种,带实例说明
    • Merge into用法总结

      2020-07-16 17:58:51
      所以就有了Merge into(Oracle 9i引入的功能)语法 merge into 目标表 a using 源表 b on(a.条件字段1=b.条件字段1 and a.条件字段2=b.条件字段2 ……) when matched then update set a.更新字段=b.字段 when not ...
    • idea中的merge into curent

      千次阅读 2021-01-05 14:29:34
      最近碰到一系列问题,正好求知所问深入学习了下git 内部原理,东西比较多,先从git merge 说起,因为merge是所有版本控制系统中最最核心之一,本文通过讨论是2个commit 之间的合并 类似git merge C1 C2 ,更多的 git...
    • Merge into的使用 之 Where 条件

      万次阅读 2019-10-08 16:26:14
      Merge是一个非常有用的功能,类似于Mysql里的insert into on duplicate key. Oracle在9i引入了merge命令, 通过这个merge你能够在一个SQL语句中对一个表同时执行inserts和updates操作. 当然是update还是insert是依据...
    • merge into的用法

      2020-07-10 15:58:03
      merge into t1 using t2 on t1.yhangh=t2.yhangh when matched then update set t1.ygongh_now=t2.ygongh,t1.bilifc_now=t2.bilifc when not matched then insert (yhangh,ygongh_now,bilifc_now) values(t2.yhangh,...
    • MERGE INTO

      2019-07-17 14:16:14
      参考资料:http://www.oracle-developer.net/content/utilities/merge_counter.sql 文档:Oracle Database SQL Reference, 10g Release 2 (10.2)...Oracle MERGE INTO的用法总结: Use the MERGE statement to selec...
    • 在plsql中第一次执行merge into,插入的数据没问题,也没报错, 执行后再点一次执行,数据居然变多了,在源数据没变的情况下,请问我代码的问题吗? 下面是SQL:...
    • merge into 缺失using关键字 今天在做项目的时候,使用merge into总是报错缺失using关键字,可是我SQL里面明明就写了using关键字,找了半天错误,发现了两个常见错误,在此记录一下。 修改前错误代码 merge into ...
    • mysql实现merge into的方法

      千次阅读 2021-04-09 17:58:54
      mysql实现merge into的方法 merge into语法 merge语法: MERGE INTO [target-table] A USING [source-table sql] B ON([conditional expression] and [...]...) WHEN MATCHED THEN [UPDATE sql] WHEN NOT ...
    • oracle中merge into using 的用法解析

      千次阅读 2019-06-27 15:40:46
      merge into的形式: MERGE INTO [target-table] A USING [source-table sql] B ON([conditional expression] and [...]...)WHEN MATCHED THEN [UPDATE sql]WHEN NOT MATCHED THEN...
    • Oracle merge into 基于大数据量的优化方向 针对Oracle数据库的更新操作我们会使用 update tableName set col1=col2 where condition... 这样子的事务提交效率比较低下,所以大部分情况下需要批量更新的话我们会...
    • 遇到现象: 该PLSQL块服务运行正常,但表S_VipShopStock的主建出现严重跳号,运行第一次max(id)为600,下一次执行id为1200,经过...merge into mbs7_oms.XS_VipShopStock a using (Select c.stylecode, c.warecode, ...
    • 求大神帮帮忙!
    • Merge Into 快速更新大量数据

      千次阅读 2020-12-12 18:14:43
      2、Merge Into Merge into 是 Oracle 9i 引入的功能,其语法如下: MERGE INTO table_name alias1 USING (table|view|sub_query) alias2 ON (join condition) WHEN MATCHED THEN UPDATE table_name SET col1 = col_...
    • Oracle merge into插入和修改数据 目录 语法: Mybatis: 单个插入: 多个插入: 总结: 在使用oracle数据库,需要操作数据,增加和删除。使用merge into 的语法可以一个语句搞定。 语法: MERGE ...
    • merge into curren 的作用是:合并到当前分支(将所选分支合并到当前分支)。 命令的位置: 1.idea的右下角 点击当前分支可以看到全部的分支列表 选择要合并的分支,点击右侧小三角,就会在菜单列表里看到 ...
    • ORACLE MERGE INTO UPDATE DELETE 用法 使用该MERGE语句从一个或多个源中选择行以进行更新或插入表或视图。您可以指定条件以确定是更新还是插入目标表或视图。 此语句是组合多个操作的便捷方式。它可以让你避免...

    空空如也

    空空如也

    1 2 3 4 5 ... 20
    收藏数 77,586
    精华内容 31,034
    关键字:

    MERgeinto