MERGE INTO 用法:判断两张表是否满足ON条件,如果满足更新目标表,如果不满足,插入目标表。
注意:如果利用dual构建临时表,一次拼接SQL不能超过1000条语句,如果查出会报ORA-01745: 无效的主机/绑定变量名
-- MERGE INTO语法
MERGE INTO [target-table] A USING [source-table sql] B ON([conditional expression] and [...]...)
WHEN MATCHED THEN
[UPDATE sql]
WHEN NOT MATCHED THEN
[INSERT sql]
-- 例子
MERGE INTO 表名 别名1
USING (table|view|sub_query) 别名2
ON (JOIN condition)
WHEN MATCHED THEN
-- 如果存在,更新
UPDATE SET
别名1.col1 = 别名2.col,
别名1.col2 = 别名2.col
WHEN NOT MATCHED THEN
-- 如果不存在,新增
INSERT (column1, column2) VALUES (别名2.column1, 别名2.column2);
mybatis中的应用
<!-- 更新操作 -->
<insert id="insertAndUpdateWithMerge" parameterType="java.util.List">
MERGE INTO 表名1 A1
USING (
<foreach collection="list" index="i" item="itm" separator="union">
<include refid="insertAndUpdate_Union_Sql"/>
</foreach>
) A2 ON (A1.ID=A2.ID)
WHEN MATCHED THEN
update set A1.STATE=A2.STATE
WHEN NOT MATCHED THEN
insert (ID,STATE) values(0, A2.STATE)
</insert>
<!-- 利用dual,将数据转化为table -->
<sql id="insertAndUpdate_Union_Sql">
select
<trim suffixOverrides=",">
<choose>
<!-- 判断对象中是否包含key -->
<when test="itm.containsKey('STATE')">
#{itm.STATE} STATE,
</when>
<otherwise>
null STATE,
</otherwise>
</choose>
</trim>
from dual
</sql>