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
)