mybatis中merge into批量使用问题
来源:互联网 发布:美臀女神爆红网络 编辑:程序博客网 时间:2024/06/12 22:03
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,
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}
转载自:http://bbs.csdn.net/topics/390940098
0 0
- mybatis中merge into批量使用问题
- ibatis中使用merge into
- Oracle中Merge into使用
- mybatis中使用replace into和 insert INTO … ON DUPLICATE KEY UPDATE批量操作
- Oracle中merge into的使用
- Oracle中merge into的使用
- Oracle中merge into的使用
- oracle 中 merge into 的使用
- Oracle中merge into的使用
- Oracle中merge into的使用
- Oracle中merge into的使用
- Oracle中merge into的使用
- Oracle中merge into的使用
- Oracle中merge into的使用
- Oracle中merge into的使用
- Oracle中merge into的使用
- Oracle中merge into的使用
- Oracle 中 merge into的使用
- ryryrjqvlha
- android使用junit进行单元测试
- 突然发现的好东西
- 通过 Jersey 客户端 API 调用 REST 风格的 Web 服务
- myBatis批量添加,修改和删除
- mybatis中merge into批量使用问题
- MyBatis的动态SQL详解
- MyBatis参数传入集合之foreach动态sql
- Ambari离线安装
- Ambari在线repo安装
- XPath 多条件查询语句
- node + Express 服务器性能实验
- Mybatis的if test字符串比较问题
- mybatis动态SQL语句