SQL - 【MERGE INTO】 - 【ON DUPLICATE KEY】 存在更新,不存在插入
1. Orace 存在更新,不存在插入的写法 【MERGE INTO】-【when not matched then insert】-【when matched then update】
<insert id="updateUserRole" > MERGE INTO SVC_ROLE_USER RU USING (SELECT #{userAccount, jdbcType=VARCHAR} as userAccount, #{roleCode, jdbcType=VARCHAR} as roleCode from dual) d on (RU.USER_ACCOUNT = d.userAccount AND RU.ROLE_CODE = d.roleCode) when not matched then insert <trim prefix="(" suffix=")" suffixOverrides=","> USER_ACCOUNT, ROLE_CODE, STATUS, CREATER, CREATED_TIME </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> #{userAccount, jdbcType=VARCHAR}, #{roleCode, jdbcType=VARCHAR}, #{status, jdbcType=VARCHAR, typeHandler=com.ch.evaluation.common.mybatis.MyBatisEnumHandlerSTATUS} , #{creater, jdbcType=VARCHAR}, SYSDATE </trim> when matched then update <set> STATUS = #{status, jdbcType=VARCHAR, typeHandler=com.ch.evaluation.common.mybatis.MyBatisEnumHandlerSTATUS} , MODIFIER = #{modifier, jdbcType=VARCHAR}, MODIFIED_TIME = SYSDATE </set> where ROLE_CODE = #{roleCode, jdbcType=VARCHAR AND USER_ACCOUNT = #{userAccount, jdbcType=VARCHAR} </insert>
2. MySql 存在更新,不存在插入的写法【INSERT INTO】-【ON DUPLICATE KEY】
<insert id="mergeSelective" parameterType="com.zhangmen.teacher.model.forge.TeacherSalaryAccount" > INSERT INTO t_biz_teacher_salary_account <trim prefix="(" suffix=")" suffixOverrides="," > <if test="teacherId != null" > teacher_id, </if> <if test="idCardNumber != null" > id_card_number, </if> <if test="bankAccount != null" > bank_account, </if> <if test="bankName != null" > bank_name, </if> <if test="bankProvince != null" > bank_province, </if> <if test="bankCity != null" > bank_city, </if> <if test="bankBranch != null" > bank_branch, </if> <if test="bankAccountMobile != null" > bank_account_mobile, </if> <if test="alipayAccount != null" > alipay_account, </if> <if test="remark != null" > remark, </if> <if test="contractStatus != null" > contract_status, </if> <if test="deleted != null" > deleted, </if> <if test="createdTime != null" > created_time, </if> <if test="updatedTime != null" > updated_time, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides="," > <if test="teacherId != null" > #{teacherId,jdbcType=INTEGER}, </if> <if test="idCardNumber != null" > #{idCardNumber,jdbcType=VARCHAR}, </if> <if test="bankAccount != null" > #{bankAccount,jdbcType=VARCHAR}, </if> <if test="bankName != null" > #{bankName,jdbcType=VARCHAR}, </if> <if test="bankProvince != null" > #{bankProvince,jdbcType=VARCHAR}, </if> <if test="bankCity != null" > #{bankCity,jdbcType=VARCHAR}, </if> <if test="bankBranch != null" > #{bankBranch,jdbcType=VARCHAR}, </if> <if test="bankAccountMobile != null" > #{bankAccountMobile,jdbcType=VARCHAR}, </if> <if test="alipayAccount != null" > #{alipayAccount,jdbcType=VARCHAR}, </if> <if test="remark != null" > #{remark,jdbcType=VARCHAR}, </if> <if test="contractStatus != null" > #{contractStatus,jdbcType=TINYINT}, </if> <if test="deleted != null" > #{deleted,jdbcType=BIT}, </if> <if test="createdTime != null" > #{createdTime,jdbcType=TIMESTAMP}, </if> <if test="updatedTime != null" > #{updatedTime,jdbcType=TIMESTAMP}, </if> </trim> ON DUPLICATE KEY <trim prefix="UPDATE" suffixOverrides="," > <if test="idCardNumber != null" > id_card_number = #{idCardNumber,jdbcType=VARCHAR}, </if> <if test="bankAccount != null" > bank_account = #{bankAccount,jdbcType=VARCHAR}, </if> <if test="bankName != null" > bank_name = #{bankName,jdbcType=VARCHAR}, </if> <if test="bankProvince != null" > bank_province = #{bankProvince,jdbcType=VARCHAR}, </if> <if test="bankCity != null" > bank_city = #{bankCity,jdbcType=VARCHAR}, </if> <if test="bankBranch != null" > bank_branch = #{bankBranch,jdbcType=VARCHAR}, </if> <if test="bankAccountMobile != null" > bank_account_mobile = #{bankAccountMobile,jdbcType=VARCHAR}, </if> <if test="alipayAccount != null" > alipay_account = #{alipayAccount,jdbcType=VARCHAR}, </if> <if test="remark != null" > remark = #{remark,jdbcType=VARCHAR}, </if> <if test="contractStatus != null" > contract_status = #{contractStatus,jdbcType=TINYINT}, </if> <if test="deleted != null" > deleted = #{deleted,jdbcType=BIT}, </if> <if test="createdTime != null" > created_time = #{createdTime,jdbcType=TIMESTAMP}, </if> <if test="updatedTime != null" > updated_time = #{updatedTime,jdbcType=TIMESTAMP}, </if> </trim> </insert>