欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Oracle merge 合并

程序员文章站 2024-01-11 08:38:28
...
@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