MERGE INTO "T_CUSTOMER_INFO" t1 USING ( select #{customer_id} as customer_id , #{name} as name from dual ) t2 ON ( t1.customer_id = t2.customer_id and t1.TASK_ID IS NULL ) WHEN MATCHED THEN ## 存在匹配记录，更新操作 UPDATE SET t1.name = t2.name ## 注意：更新的value值是 `t2.name` WHEN NOT MATCHED THEN ## 不存在匹配记录，新增操作 INSERT ( customer_id, name ) VALUES( t2.customer_id, ## 注意：新增的value值取`t2`表的字段 t2.name )
使用foreach语法，批量新增/更新示例：
<!-- Mapper 接口：void batchUpsert(@Param("list") List<Customer> list); --> <insert id="batchUpsert" parameterType="java.util.List" databaseId="oracle"> MERGE INTO t_customer T USING ( <foreach collection="list" item="c" separator="UNION ALL"> SELECT #{c.id} AS id, #{c.account} AS account, #{c.email} AS email, #{c.phone} AS phone, #{c.gender} AS gender, #{c.status} AS status, #{c.createTime}AS create_time, #{c.updateTime}AS update_time, #{c.BASIC_INFO}AS BASIC_INFO, #{c.OIL_STATION_CODE} AS OIL_STATION_CODE FROM dual </foreach> ) S ON (T.phone = S.phone) WHEN MATCHED THEN UPDATE SET T.update_time = SYSTIMESTAMP, T.OIL_STATION_CODE = S.OIL_STATION_CODE, T.BASIC_INFO = S.BASIC_INFO WHEN NOT MATCHED THEN INSERT (id, account, email, phone, gender, status, create_time, update_time, BASIC_INFO, OIL_STATION_CODE) VALUES (S.id, S.account, S.email, S.phone, S.gender, S.status, S.create_time, S.update_time, S.BASIC_INFO, S.OIL_STATION_CODE); </insert>
