mybatis中merge into批量使用问题

[小二打酱油]

<insert id="insertAttractionsBatch" parameterType="java.util.List">
MERGE INTO TM_USER_CART_INFO T1
USING
(
<foreach collection="list" item="item" index="index" separator="union" >
select (SELECT T2.F_CART_ID FROM TM_USER_CART_INFO T2
WHERE NOT EXISTS (SELECT '' FROM TM_USER_CART_MARKET T3 WHERE T3.F_CART_ID = T2.F_CART_ID)
AND T2.F_CHANNEL_NUM=#{item.channelNum} AND T2.F_USER_ID = #{item.userId} AND T2.F_GOODS_NUM = #{item.goodsNum} AND T2.F_REGION_NUM = #{item.regionNum}
) F_CART_ID ,#{item.goodsNum} F_GOODS_NUM from dual
</foreach>
) T
ON
(
T1.F_CART_ID = T.F_CART_ID AND T1.F_GOODS_NUM = T.F_GOODS_NUM
)
WHEN MATCHED THEN

UPDATE SET T1.F_GOODS_COUNT = T1.F_GOODS_COUNT + #{item.goodsCount},T1.F_UPDATE_TIME = (SELECT TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') FROM DUAL)
WHEN NOT MATCHED THEN
INSERT(F_CART_ID,F_CHANNEL_NUM,F_MERCHANT_NUM,F_USER_ID,F_GOODS_NUM,F_GOODS_COUNT,F_ADD_TIME,F_REGION_NUM)
VALUES(#{item.cartId},#{item.channelNum},'',#{item.userId},#{item.goodsNum},#{item.goodsCount},(SELECT TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') FROM DUAL),#{item.regionNum})

</insert>



update 和 insert 部分参数应该怎么填写？我试过#{item.cartId} 发现使用的是用一个值，直接#{cartId}会找不到。求大神指点。

[小二打酱油]

问题解决了。insert 和update中所有的数据都需要从using中获取，这样就能达到批量merge into的效果了 MERGE INTO TM_USER_CART_INFO T1 USING ( <foreach collection="list" item="item" index="index" separator="union" > SELECT CASE WHEN TEMP1.F_CART_ID IS NULL THEN TEMP.F_CART_ID ELSE TEMP1.F_CART_ID END F_CART_ID, TEMP.F_GOODS_NUM, TEMP.F_USER_ID, TEMP.F_REGION_NUM, TEMP.F_CHANNEL_NUM, TEMP.F_GOODS_COUNT FROM (SELECT #{cartId} F_CART_ID,#{goodsNum} F_GOODS_NUM,#{userId} F_USER_ID,#{regionNum} F_REGION_NUM,#{channelNum} F_CHANNEL_NUM,#{goodsCount} F_GOODS_COUNT FROM DUAL) TEMP LEFT JOIN ( SELECT * FROM TM_USER_CART_INFO T2 WHERE 1=1 AND T2.F_USER_ID = #{userId} AND T2.F_GOODS_NUM = #{goodsNum} AND T2.F_REGION_NUM = #{regionNum} AND T2.F_CHANNEL_NUM = #{channelNum} and NOT EXISTS (SELECT '' FROM TM_USER_CART_MARKET T3 WHERE T3.F_CART_ID = T2.F_CART_ID) )TEMP1 ON TEMP1.F_GOODS_NUM = TEMP.F_GOODS_NUM </foreach> ) T ON (T1.F_CART_ID = T.F_CART_ID) WHEN MATCHED THEN UPDATE SET T1.F_GOODS_COUNT = T1.F_GOODS_COUNT + T.F_GOODS_COUNT, T1.F_UPDATE_TIME = (SELECT TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') FROM DUAL) WHEN NOT MATCHED THEN INSERT (F_CART_ID, F_CHANNEL_NUM, F_MERCHANT_NUM, F_USER_ID, F_GOODS_NUM, F_GOODS_COUNT, F_ADD_TIME, F_REGION_NUM) VALUES (T.F_CART_ID, T.F_CHANNEL_NUM, '', T.F_USER_ID, T.F_GOODS_NUM, T.F_GOODS_COUNT, (SELECT TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') FROM DUAL), T.F_REGION_NUM)

[小二打酱油]

上面写错了，#{xxxx} 改成 #{item.xxxx}

[tony4geek]

{item.cartId}, 没在 foreach 里面？