@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