<update id = "updateAccomodationStandard" merge into MFM_ACCOMMODATION_STANDARD a using ( <foreach collection="areaIds" item="areaId" separator="union"> select #{areaId，jdbcType=VARCHAR} AREA_ID, #{rankId，jdbcType=VARCHAR} RANK_ID, #{year，jdbcType=VARCHAR} YEAR from dual </foreach> )c on ( a.AREA ID = c.AREA_ID and a.RANK_ID = c.RANK_ID and a.YEAR = c.YEAR when matched then update set a.STAND_VALVE = #{standValue}, a.IS_SHOW = 1 when not matched then insent (a.ID,a.AREA_ID, a.RANK_ID,a.YEAR,a.STANDVALUE, a.order_id, a.IS_SHOW)values (null,C.AREA_ID, #{rankId),#{year},#{standValve},#{order},1) </update>改写为mysql

UPDATE MFM_ACCOMMODATION_STANDARD a
JOIN
(
SELECT
#{areaId} AS AREA_ID,
#{rankId} AS RANK_ID,
#{year} AS YEAR
FROM dual
UNION
SELECT
#{areaId2} AS AREA_ID,
#{rankId2} AS RANK_ID,
#{year2} AS YEAR
FROM dual
-- 可以加入更多的 areaIds
) c
ON a.AREA_ID = c.AREA_ID AND a.RANK_ID = c.RANK_ID AND a.YEAR = c.YEAR
WHEN MATCHED THEN
UPDATE SET a.STAND_VALVE = #{standValue}, a.IS_SHOW = 1
WHEN NOT MATCHED THEN
INSERT (ID, AREA_ID, RANK_ID, YEAR, STANDVALUE, order_id, IS_SHOW)
VALUES (null, c.AREA_ID, #{rankId}, #{year}, #{standValue}, #{order}, 1)