merge into
test_id a
using
(select id , name from test_id ) b
on (a.id = b.id)
WHEN MATCHED THEN
UPDATE SET a.name = b.name
WHEN NOT MATCHED THEN
INSERT
(a.id, a.name)
VALUES
(b.id, b.name);
例子:
入参:对象
表b根据入参创建,然后判断表b和表a主键是否冲突,冲突则进行update;否则进行insert
<insert id="replaceSelective" databaseId="oracle" parameterType="com.synjones.cloudcard.attence.infra.entity.AttenceTempWorkEntity" >
merge into
attence_temp_work a
using
(select
#{custMemberId,jdbcType=CHAR} CUST_MEMBER_ID,
#{date,jdbcType=CHAR} "DATE",
#{custId,jdbcType=CHAR} CUST_ID,
#{workTimeId,jdbcType=INTEGER} WORK_TIME_ID,
#{workNature,jdbcType=CHAR} WORK_NATURE,
#{overWorkFlag,jdbcType=CHAR} OVER_WORK_FLAG,
#{overWorkTime,jdbcType=INTEGER} OVER_WORK_TIME,
#{createdOperCode,jdbcType=CHAR} CREATED_OPER_CODE,
#{createdStamp,jdbcType=TIMESTAMP} CREATED_STAMP,
#{lastUpdatedStamp,jdbcType=TIMESTAMP} LAST_UPDATED_STAMP,
#{lastUpdatedOperCode,jdbcType=CHAR} LAST_UPDATED_OPER_CODE,
#{workAroundId,jdbcType=INTEGER} WORK_AROUND_ID
from dual
) b
on(a.CUST_MEMBER_ID=b.CUST_MEMBER_ID and a."DATE"=b."DATE")
when not matched then
insert
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="custMemberId != null" >
a.cust_member_id,
</if>
<if test="date != null" >
a."DATE",
</if>
<if test="custId != null" >
a.cust_id,
</if>
<if test="workTimeId != null" >
a.work_time_id,
</if>
<if test="workNature != null" >
a.work_nature,
</if>
<if test="overWorkFlag != null" >
a.over_work_flag,
</if>
<if test="overWorkTime != null" >
a.over_work_time,
</if>
<if test="createdOperCode != null" >
a.created_oper_code,
</if>
<if test="createdStamp != null" >
a.created_stamp,
</if>
<if test="lastUpdatedStamp != null" >
a.last_updated_stamp,
</if>
<if test="lastUpdatedOperCode != null" >
a.last_updated_oper_code,
</if>
<if test="workAroundId != null" >
a.work_around_id,
</if>
</trim>
<trim prefix="VALUES (" suffix=")" suffixOverrides="," >
<if test="custMemberId != null" >
#{custMemberId,jdbcType=CHAR},
</if>
<if test="date != null" >
#{date,jdbcType=CHAR},
</if>
<if test="custId != null" >
#{custId,jdbcType=CHAR},
</if>
<if test="workTimeId != null" >
#{workTimeId,jdbcType=INTEGER},
</if>
<if test="workNature != null" >
#{workNature,jdbcType=CHAR},
</if>
<if test="overWorkFlag != null" >
#{overWorkFlag,jdbcType=CHAR},
</if>
<if test="overWorkTime != null" >
#{overWorkTime,jdbcType=INTEGER},
</if>
<if test="createdOperCode != null" >
#{createdOperCode,jdbcType=CHAR},
</if>
<if test="createdStamp != null" >
#{createdStamp,jdbcType=TIMESTAMP},
</if>
<if test="lastUpdatedStamp != null" >
#{lastUpdatedStamp,jdbcType=TIMESTAMP},
</if>
<if test="lastUpdatedOperCode != null" >
#{lastUpdatedOperCode,jdbcType=CHAR},
</if>
<if test="workAroundId != null" >
#{workAroundId,jdbcType=INTEGER},
</if>
</trim>
when matched then
UPDATE
<set >
<if test="custId != null" >
a.cust_id = #{custId,jdbcType=CHAR},
</if>
<if test="workTimeId != null" >
a.work_time_id = #{workTimeId,jdbcType=INTEGER},
</if>
<if test="workNature != null" >
a.work_nature = #{workNature,jdbcType=CHAR},
</if>
<if test="overWorkFlag != null" >
a.over_work_flag = #{overWorkFlag,jdbcType=CHAR},
</if>
<if test="overWorkTime != null" >
a.over_work_time = #{overWorkTime,jdbcType=INTEGER},
</if>
<if test="createdOperCode != null" >
a.created_oper_code = #{createdOperCode,jdbcType=CHAR},
</if>
<if test="createdStamp != null" >
a.created_stamp = #{createdStamp,jdbcType=TIMESTAMP},
</if>
<if test="lastUpdatedStamp != null" >
a.last_updated_stamp = #{lastUpdatedStamp,jdbcType=TIMESTAMP},
</if>
<if test="lastUpdatedOperCode != null" >
a.last_updated_oper_code = #{lastUpdatedOperCode,jdbcType=CHAR},
</if>
<if test="workAroundId != null" >
a.work_around_id = #{workAroundId,jdbcType=INTEGER},
</if>
</set>
where a.cust_member_id = #{custMemberId,jdbcType=CHAR}
and a."DATE" = #{date,jdbcType=CHAR}
</insert>