mybatis에서 아래처럼 작성하였는데
( ERROR: syntax error at or near "UPDATESET" )
자바에서 이와 같은 에러가 나오네요.
<insert id ="excel_cartUSA" parameterType ="java.util.List" >
<foreach collection ="list" item ="element" index ="index" >
WITH tempSelect (country, b_Name, partNum, description, qty, id, vinNum, memo)
AS
(
SELECT country, b_name, partNum, description, #{element.qty} AS qty, #{element.id} AS id, #{element.vinNum} AS vinNum, #{element.memo} AS memo FROM vParts_All
WHERE (partNum=#{element.partNum} AND country='USA')
)
INSERT INTO cart(country, b_Name, partNum, description, qty, id, vinNum, memo)
SELECT * FROM tempSelect ON CONFLICT(partNum) DO UPDATESET qty=#{element.qty} vinNum=#{element.vinNum}, memo=#{element.memo}
<foreach >
<insert >
INSERT 하는데 문제가 발생되나 (?) 해서 아래처럼 UPSERT문으로 바꾸었더니
<insert id ="excel_cartUSA" parameterType ="java.util.List" >
<foreach collection ="list" item ="element" index ="index" >
WITH tempSelect (country, b_Name, partNum, description, qty, id, vinNum, memo)
AS
(
SELECT country, b_name, partNum, description, #{element.qty} AS qty, #{element.id} AS id, #{element.vinNum} AS vinNum, #{element.memo} AS memo FROM vParts_All
WHERE (partNum=#{element.partNum} AND country='USA')
)
,upsert AS
(
UPDATE cart SET qty=#{element.qty}, vinNum=#{element.vinNum}, memo=#{element.memo} WHERE partNum=#{element.partNum} RETURNING *
)
INSERT INTO cart (country, b_Name, partNum, description, qty, id, vinNum, memo)
SELECT * FROM tempSelect WHERE NOT EXISTS (SELECT * FROM upsert)
<foreach >
<insert >
(ERROR: syntax error at or near "WITH")
이런 에러가 또 나옵니다.
뭐가 문제일까요? |