Oracle merge 合并-优快云博客

本文链接：https://blog.youkuaiyun.com/yifeixiang/article/details/84490859

本文详细介绍了使用@Transaction注解进行数据库操作的策略，包括如何通过SQL语句合并临时表中的数据到主表，并同时删除临时表中的所有信息。通过具体的例子演示了使用MyBatis框架实现这一过程，包括SQL语句的编写和数据库操作的整合。此外，还强调了创建唯一索引的重要性以提高查询效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >


@Transactional
	public void mergeAll(){
		dao.executeUpdateBySqlId("mergeTbDdbLogInfo", null); 
		dao.executeUpdateBySqlId("deletTbDdbLogTEMPAllInfo", null); 
	}


<update id="mergeTbDdbLogInfo">
		<![CDATA[
			merge into tb_ddb_log a
using (select * from tb_ddb_log_temp) b 
on (a.paperNo= b.paperNo 
            and a.paperName= b.paperName
            and a.username= b.username
            and  a.telephone= b.telephone 
            and a.address= b.address 
            and  a.zipCode= b.zipCode
            and a.delflag = 0
        )
        when not matched then
         insert values
            (b.row_id,
        b.createtime,
        b.updatetime,
        b.createuserid,
        b.updateuserid,
        b.DEPTID,
        b.DELFLAG,
        b.paperNo,
        b.paperName,
        b.username,
        b.telephone,
        b.address,
        b.zipCode)
		]]>
	</update>


<update id="mergeYjxxInfo">
		<![CDATA[
			merge into tb_yjxx a 
using (select * from tb_yjxx_tmp) b
on (a.ghtm= b.ghtm)
when matched then 
      update set 
            a.DEPTID = b.deptid,
            a.delflag = b.DELFLAG ,
            a.LSH = b.LSH ,
            a.PCH = b.PCH ,
            a.TDJ = b.TDJ ,
            a.TDJH=b.TDJH,
            a.TDDH=b.TDDH,
            a.TDFJ=b.TDFJ,
            a.CLZT1 =b.CLZT1 ,
            a.CLZT2 =b.CLZT2 ,
            a.CLSJ=b.CLSJ,
            a.CZY =b.CZY ,
            a.QX=b.QX,
            a.KHH =b.KHH ,
            a.ZH=b.ZH,
            a.DWMC=b.DWMC,
            a.XM=b.XM,
            a.DZ=b.DZ,
            a.LXDH=b.LXDH,
            a.JE=b.JE,
            a.DZDBH =b.DZDBH ,
            a.WDBZ=b.WDBZ
when not matched then
   insert values(b.row_id,b.createtime,b.updatetime,b.createuserid,b.updateuserid)
		]]>
	</update>


<delete id="deletTbDdbLogTEMPAllInfo">
		delete from TB_DDB_LOG_TEMP
	</delete>

mysql
需要建一个唯一索引


  CREATE UNIQUE INDEX TELEPHONE_INDEX ON JSHX_PROGRAM(TELEPHONE,BSS_TYPE);


replace into JSHX_PROGRAM
			select * from JSHX_PROGRAM_TEMP where delflag = 0