on duplicate key update 的意思是插入的数据中存在与唯一主键相同的即存在则更新,不存在则插入,适用于mysql数据库,oracle数据库中使用
MERGE INTO ..... using ...on ...when matched then ... when not matched then
实例如下:
mysql:
insert into table (PID,SPWID,BTCD,SV,BXCD,BSW,BW,DSW,SC,NT,MODITIME)
VALUES
<foreach collection="list" item="bt" index="index" separator=",">
( #{bt.pid},#{bt.spwid},#{bt.btcd},#{bt.sv},#{bt.bxcd},#{bt.bsw},#{bt.bw},#{bt.dsw},#{bt.sc},#{bt.nt},#{bt.moditime} )
</foreach>
ON DUPLICATE KEY UPDATE
pid = values(pid),
spwid = values(spwid),
btcd = values(btcd),
sv = values(sv),
bxcd = values(bxcd),
bsw = values(bsw),
bw = values(bw),
dsw = values(dsw),
sc = values(sc),
nt = values(nt),
moditime = values(moditime)
对应oracle:
MERGE INTO table t2 USING( <foreach collection="list" item="bt" index="index" separator="union all"> SELECT #{bt.pid,jdbcType=VARCHAR} PID, #{bt.spwid,jdbcType=VARCHAR} SPWID, #{bt.btcd,jdbcType=VARCHAR} BTCD, #{bt.sv,jdbcType=DOUBLE} SV, #{bt.bxcd,jdbcType=VARCHAR} BXCD, #{bt.bsw,jdbcType=DOUBLE} BSW, #{bt.bw,jdbcType=DOUBLE} BW, #{bt.dsw,jdbcType=DOUBLE} DSW, #{bt.sc,jdbcType=DOUBLE} SC, #{bt.nt,jdbcType=VARCHAR} NT, #{bt.moditime,jdbcType=DATE} MODITIME from DUAL </foreach> )t1 ON (t2.PID = t1.PID) WHEN MATCHED THEN UPDATE SET t2.SPWID=t1.SPWID, t2.BTCD=t1.BTCD, t2.SV=t1.SV, t2.BXCD=t1.BXCD, t2.BSW=t1.BSW, t2.BW=t1.BW, t2.DSW=t1.DSW, t2.SC=t1.SC, t2.NT=t1.NT, t2.MODITIME=t1.MODITIME WHEN NOT MATCHED THEN INSERT (PID,SPWID,BTCD,SV,BXCD,BSW,BW,DSW,SC,NT,MODITIME) VALUES (t1.PID,t1.SPWID,t1.BTCD,t1.SV,t1.BXCD,t1.BSW,t1.BW,t1.DSW,t1.SC,t1.NT,t1.MODITIME)
版权声明:本文为luwei_cool原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接和本声明。