oralce 的 merge 操作

最新推荐文章于 2024-04-21 22:54:50 发布

原创最新推荐文章于 2024-04-21 22:54:50 发布 · 192 阅读

0 ·

CC 4.0 BY-SA版权

杂七杂八专栏收录该内容

13 篇文章

订阅专栏

本文介绍了一种使用MERGE语句来更新或插入商品ASIN周预测数据的方法。该过程通过比较数据库中已存在的记录与待更新的数据，实现对特定商品在组织单元下按预测类型和目标日期的数量覆盖或新增。此操作还记录了最后更新时间和操作员ID等元数据。

<insert id="mergeOverride">
MERGE INTO @@schema.invods_asin_weekly_forecasts T1
USING (
SELECT #{asin} asin , #{organizationalUnitId} organizational_unit_id , #{forecastTargetDay} forecast_target_day , #{forecastTypeCode} forecast_type_code
FROM DUAL) T2
ON ( T1.asin = T2.asin
AND T1.organizational_unit_id = T2.organizational_unit_id
AND T1.forecast_type_code = T2.forecast_type_code
AND T1.forecast_target_day = T2.forecast_target_day )
WHEN MATCHED THEN
UPDATE
SET
T1.quantity =#{overrideQuantity},
T1.override_logon_id =#{overrideLogonId},
T1.last_updated =#{lastUpdated}
WHEN NOT MATCHED THEN
INSERT (T1.asin, T1.organizational_unit_id, T1.gl_product_group, T1.forecast_type_code, T1.forecast_target_day,
T1.forecast_creation_day, T1.quantity, T1.forecast_dist_type_id, T1.forecast_dist_param_1,
T1.forecast_dist_param_2, T1.last_updated, T1.override_logon_id) VALUES
(#{asin},#{organizationalUnitId},#{gl},#{forecastTypeCode},#{forecastTargetDay},
#{forecastCreationDay},#{overrideQuantity},#{forecastDistTypeId},#{forecastDistParm1},
#{forecastDistParm2},#{lastUpdated},#{overrideLogonId})
</insert>