insertOrUpdateBatch样例代码

最新推荐文章于 2024-04-11 15:33:37 发布

丶意冷

最新推荐文章于 2024-04-11 15:33:37 发布

阅读量563

点赞数

分类专栏： javaEE的bug 文章标签：数据库 java sql

本文链接：https://blog.csdn.net/weixin_43992455/article/details/128150330

版权

javaEE的bug 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

insertOrUpdateBatch需要注意的点

<insert id="insertOrUpdateFutureLimitPositionValueBatch">
        merge into tusr_futurelimitpositionvalue a
        USING
        (
        <foreach collection="list" item="item" separator="union all">
            (select
            #{item.futureLimitPositionId} as future_limit_position_id,
            #{item.limitPositionPointId} as limit_position_point_id,
            #{item.compareDir,jdbcType=CHAR} as compare_dir,
            #{item.positionQty,jdbcType=DECIMAL} as position_qty,
            #{item.thresholdType,jdbcType=CHAR} as threshold_type,
            #{item.thresholdValue,jdbcType=DECIMAL} as threshold_value
            from dual
            )
        </foreach>
        ) t
        ON (a.future_limit_position_id = t.future_limit_position_id and a.limit_position_point_id = t.limit_position_point_id)
        WHEN MATCHED THEN
        UPDATE
        <set>
            a.compare_dir = t.compare_dir,
            a.position_qty = t.position_qty,
            a.threshold_type = t.threshold_type,
            a.threshold_value = t.threshold_value
        </set>
        WHEN NOT MATCHED THEN
        insert
        (
        a.future_limit_position_id,
        a.limit_position_point_id,
        a.compare_dir,
        a.position_qty,
        a.threshold_type,
        a.threshold_value
        )
        VALUES
        (
        t.future_limit_position_id,
        t.limit_position_point_id,
        t.compare_dir,
        t.position_qty,
        t.threshold_type,
        t.threshold_value
        )
    </insert>

注意最后一个字段不能有多余的逗号，否则会报无效字段问题
在merge时所比较的on的字段不能与update语句中的字段重复，否则会报

Error occurred during SQL query execution

原因：
 SQL 错误 [38104] [99999]: ORA-38104: 无法更新 ON 子句中引用的列: "A"."FUTURE_LIMIT_POSITION_ID"

错误案例:

merge into tusr_futurelimitpositionvalue a
        USING
        (
        <foreach collection="list" item="item" separator="union all">
            (select
            #{item.futureLimitPositionId} as future_limit_position_id,
            #{item.limitPositionPointId} as limit_position_point_id,
            #{item.compareDir,jdbcType=CHAR} as compare_dir,
            #{item.positionQty,jdbcType=DECIMAL} as position_qty,
            #{item.thresholdType,jdbcType=CHAR} as threshold_type,
            #{item.thresholdValue,jdbcType=DECIMAL} as threshold_value
            from dual
            )
        </foreach>
        ) t
        ON (a.future_limit_position_id = t.future_limit_position_id and a.limit_position_point_id = t.limit_position_point_id)
        WHEN MATCHED THEN
        UPDATE
        <set>
            a.compare_dir = t.compare_dir,
            a.position_qty = t.position_qty,
            a.threshold_type = t.threshold_type,
            a.threshold_value = t.threshold_value
        </set>
        WHEN NOT MATCHED THEN
        insert
        (
        a.future_limit_position_id,
        a.limit_position_point_id,
        a.compare_dir,
        a.position_qty,
        a.threshold_type,
        a.threshold_value
        )
        VALUES
        (
        t.future_limit_position_id,
        t.limit_position_point_id,
        t.compare_dir,
        t.position_qty,
        t.threshold_type,
        t.threshold_value
        )