데이터베이스 질문과 답변
<insert id="addExcelUser" parameterType="java.util.List">
MERGE INTO cops_ivmms_userlist as ciu
USING (
<foreach collection="list" item="item" open="" close="">
SELECT
1 AS dual,
#{item.USERID} as USERID,
#{item.USERNM} as USERNM,
#{item.uploaddate} as uploaddate
</foreach>
) AS b
on
(ciu.USERID = b.USERID)
WHEN MATCHED THEN
UPDATE SET uploaddate = b.uploaddate
WHEN NOT MATCHED THEN
INSERT(USERID, USERNM, uploaddate)
VALUES
(
b.USERID,
b.USERNM,
b.uploaddate
)
</insert>
merge문 작성해서 foreach돌리려하니 구문오류인지 실행이 안되네요..
by 뉴비디비
[2022.02.02 21:58:42]
-- foreach 안에서 SELECT 를 여러번 쓸꺼면 UNION ALL 로 연결하셔야 할꺼예요 USING ( SELECT 1 AS DUAL, id_1, nm_1, dt_1 UNION ALL SELECT 2 AS DUAL, id_2, nm_2, dt_2 UNION ALL SELECT 3 AS DUAL, id_3, nm_3, dt_3 ........ ) AS b
) 버튼을 클릭하여 작성 하시면 됩니다.