<update id="saveGroupCompany" parameterType="hashmap">
MERGE INTO HMG.TB_FOOTER_GROUP A
USING(
<foreach collection="list" item="item" index="index" separator="UNION" >
SELECT
#{item.groupId, jdbcType=NUMERIC} as groupId
, #{item.langCd} as langCd
, #{item.groupNm} as groupNm
, #{item.groupLink} as groupLink
, #{item.sortNo} as sortNo
, #{__param.userId} as userId
FROM DUAL
</foreach>
) B
ON (A.GROUP_ID = B.groupId)
WHEN MATCHED THEN
UPDATE SET
A.LANG_CD = B.langCd
, A.GROUP_NM = B.groupNm
, A.GROUP_LINK = B.groupLink
, A.SORT_NO = B.sortNo
, A.UPDATE_USER =
CASE WHEN A.LANG_CD = B.langCd
AND A.GROUP_NM = B.groupNm
AND A.GROUP_LINK = B.groupLink
AND A.SORT_NO = B.sortNo
THEN A.UPDATE_USER
ELSE B.userId
END
, A.UPDATE_DATE =
CASE WHEN A.LANG_CD = B.langCd
AND A.GROUP_NM = B.groupNm
AND A.GROUP_LINK = B.groupLink
AND A.SORT_NO = B.sortNo
THEN A.UPDATE_DATE
ELSE CURRENT_TIMESTAMP
END
WHEN NOT MATCHED THEN
INSERT (
A.GROUP_ID
, A.LANG_CD
, A.GROUP_NM
, A.GROUP_LINK
, A.SORT_NO
, A.CREATE_USER
, A.CREATE_DATE
, A.UPDATE_USER
, A.UPDATE_DATE
) VALUES (
HMG.SEQ_FOOTER_GROUP.NEXTVAL
, B.langCd
, B.groupNm
, B.groupLink
, B.sortNo
, B.userId
, CURRENT_TIMESTAMP
, B.userId
, CURRENT_TIMESTAMP
)
</update>
위 쿼리에서 중간 부분
WHEN MATCHED THEN
UPDATE SET
A.LANG_CD = B.langCd
, A.GROUP_NM = B.groupNm
, A.GROUP_LINK = B.groupLink
, A.SORT_NO = B.sortNo
, A.UPDATE_USER =
CASE WHEN A.LANG_CD = B.langCd
AND A.GROUP_NM = B.groupNm
AND A.GROUP_LINK = B.groupLink
AND A.SORT_NO = B.sortNo
THEN A.UPDATE_USER
ELSE B.userId
END
, A.UPDATE_DATE =
CASE WHEN A.LANG_CD = B.langCd
AND A.GROUP_NM = B.groupNm
AND A.GROUP_LINK = B.groupLink
AND A.SORT_NO = B.sortNo
THEN A.UPDATE_DATE
ELSE CURRENT_TIMESTAMP
END
WHEN NOT MATCHED THEN
여기 부분 중간 CASE문에 AND이렇게 여러번 달아도 되나요?
뭔가 안되는거 같은...그런 느낌인데...잘 모르겠어서 질문드립니다!
안되는거라면 보통 어떻게 해야 할지...도와주세요~!