반응형
//리스트 길이 만큼 반복해서 COUNT
<select id="selectDataVariableDeleteCheck" parameterType="DataVariableVO" resultType="int">
<foreach collection="varSeqList" item="varSeq" separator=" UNION ALL ">
SELECT COUNT(*)
FROM public.cdp_dataset_request_variable
WHERE var_seq = #{varSeq}
</foreach>
</select>
// COUNT값 개수에 따라 1, 0 출력
<choose>
<when test="count(varSeqList) > 0">
<!-- If varSeqList is not empty -->
<foreach collection="varSeqList" item="varSeq" separator=" UNION ALL ">
SELECT COUNT(*) AS count_result
FROM public.cdp_dataset_request_variable
WHERE var_seq = #{varSeq}
</foreach>
<if test="count(varSeqList) > 1">
<!-- If more than one result, sum the count_result values -->
SELECT SUM(count_result) AS final_result
FROM (
<foreach collection="varSeqList" item="varSeq" separator=" UNION ALL ">
SELECT COUNT(*) AS count_result
FROM public.cdp_dataset_request_variable
WHERE var_seq = #{varSeq}
</foreach>
) AS counts
</if>
</when>
<otherwise>
<!-- If varSeqList is empty, return 0 -->
SELECT 0 AS final_result
</otherwise>
</choose>
//리스트 데이터와 일치하는 row 삭제
<delete id="deleteDataVariables" parameterType="DataVariableVO">
DELETE FROM public.cdp_data_variable
WHERE varSeq IN
<foreach collection="varSeqList" item="varSeq" open="(" separator="," close=")">
#{varSeq}
</foreach>
</delete>
버전 업을 위한 쿼리
/*col1인 데이터 col2값 1 증가시키기*/
UPDATE public.sample
SET col2 = (col2 + 1)
WHERE col1 = #{col1}
AND col2 = (
SELECT COALESCE(MAX(col2), 0)
FROM sample
WHERE col1 = #{col1}
);
반응형
'Backend > DB 실무' 카테고리의 다른 글
[MySQL] bat파일을 이용한 export import (0) | 2024.03.18 |
---|---|
[SQL] 문자 처리 (0) | 2024.03.18 |
[SQL] SELECT한 결과 Insert 혹은 Update (0) | 2024.02.28 |
[Mybatis] 반복문 및 bind (0) | 2024.02.26 |
[Mybatis] WHERE을 조금 더 편하게 사용하기 (0) | 2024.02.26 |