merge into 语法
MERGE INTO [target-table] T USING [source-table sql] S
ON([conditional expression] and [...]...)
WHEN MATCHED
THEN [UPDATE sql]
WHEN NOT MATCHED
THEN [INSERT sql]
实际实现功能,导入接口清单表,根据接口编号判断,存在即更新,不存在即插入,separator="union all" 和 from dual的作用是将每行数据合并成一张虚拟临时表数据
<update id="saveOrUpdateInterfaceList">
MERGE INTO INTERFACE_LIST a
USING(
<foreach collection="list" index="index" item="interfaceList"
separator="union all">
SELECT
#{interfaceList.id,jdbcType=VARCHAR} id,
#{interfaceList.projectId,jdbcType=VARCHAR} projectId,
#{interfaceList.interfaceCode,jdbcType=VARCHAR} interfaceCode,
#{interfaceList.requestSys,jdbcType=VARCHAR} requestSys,
#{interfaceList.responseSys,jdbcType=VARCHAR} responseSys,
#{interfaceList.interfaceMode,jdbcType=VARCHAR} interfaceMode,
#{interfaceList.description,jdbcType=VARCHAR} description,
#{interfaceList.dataFlow,jdbcType=VARCHAR} dataFlow,
#{interfaceList.protocol,jdbcType=VARCHAR} protocol,
#{interfaceList.interfaceStatus,jdbcType=VARCHAR} interfaceStatus,
#{interfaceList.prdTime,jdbcType=VARCHAR} prdTime,
#{interfaceList.remarks,jdbcType=VARCHAR} remarks
from dual
</foreach>
)b
ON (a.INTERFACE_CODE = b.interfaceCode)
when matched then
UPDATE SET a.PROJECT_ID = b.projectId,a.REQUEST_SYS = b.requestSys,
a.RESPONSE_SYS = b.responseSys,
a.INTERFACE_MODE = b.interfaceMode,
a.DESCRIPTION =
b.description,a.DATA_FLOW = b.dataFlow,
a.PROTOCOL = b.protocol,
a.INTERFACE_STATUS = b.interfaceStatus,
a.PRD_TIME = b.prdTime,
a.REMARKS = b.remarks
where a.PROJECT_ID = B.projectId
when not matched then
INSERT(a.ID,a.PROJECT_ID,a.INTERFACE_CODE,a.REQUEST_SYS,
a.RESPONSE_SYS,a.INTERFACE_MODE,
a.DESCRIPTION,a.DATA_FLOW,a.PROTOCOL,
a.INTERFACE_STATUS,a.PRD_TIME,a.REMARKS)
VALUES (b.id,b.projectId,b.interfaceCode,b.requestSys,
b.responseSys,b.interfaceMode,
b.description,b.dataFlow,b.protocol,
b.interfaceStatus,
b.prdTime,b.remarks)
</update>
注意,update 和 insert的数据值需要在using中取