参考: https://www.cnblogs.com/relucent/p/4166544.html 当我们遇到不存在就保存,存在即更新的需求的时,MERGE INTO 是我们最好的选择。
MERGE INTO [your table-name] [rename your table here]
USING ( [write your query here] )[rename your query-sql and using just like a table]
ON ([conditional expression here] AND [...]...)
WHEN MATCHED THEN [here you can execute some update sql or something else ]
WHEN NOT MATCHED THEN [execute something else here ! ]
例子:
MERGE INTO USER T1
USING (SELECT userId FROM dual ) T2
ON ( T1.userId =T2.userId AND T1.userType =T2.userType )
WHEN MATCHED THEN
UPDATE
SET
T1.userName=?
WHERE T1.userId = T2.userId AND T1.userType =T2.userType
WHEN NOT MATCHED THEN
INSERT
(userId, userName)
VALUES
(?,?
)
Mybatis 中批量提交的例子:
<insert id="saveUser" parameterType="java.util.List">
MERGE INTO userT1
USING (SELECT #{item.userId} as userId
,#{item.userType} as userType
FROM dual ) T2
ON ( T1.userId=T2.userId and T1.userType =T2.userType)
WHEN MATCHED THEN
UPDATE
SET
T1.userName=#{item.userName,jdbcType=VARCHAR}
WHERE T1.userId=T2.userId and T1.userType =T2.userType
WHEN NOT MATCHED THEN
INSERT
(userName)
VALUES
(#{item.userName}
)
</insert>
USING搜索所有的符合条件的数据,用ON 中表达式进行比较, 如果存在执行更新操作,不存在就存入.
注: update 中的 where 的表达式 应该与ON 的表达式完全一样。