오라클 MERGE - 다중 행 업데이트 (같은 테이블에서)

A	B	C	D
1	1	1001101	KEY
1	1	1001001	KEY

 

 

C 컬럼이 더 큰 수를 가진 행의 B =B + 1 ,

작은 값을 가진 행의 A = 2 로 업데이트 (D는 2개의 행을 구분할 수 있는 값)

A	B	C	D
1	2	1001101	KEY
2	1	1001001	KEY

MERGE INTO TB_TRANSFER T
    USING (
        SELECT 
            MAX(SUBSTR(C,4,2)) AS MAX_C,
            MIN(SUBSTR(C,4,2)) AS MIN_C
            
        FROM 
            TB_TRANSFER
        WHERE 
             LENGTH(C)= 7 
       -- GROUP BY 
    ) C_INFO
    ON (
        T.D = C_INFO.D 
        AND SUBSTR(T.C,4,2) IN (C_INFO.MAX_C, C_INFO.MIN_C)
        AND C_INFO.MAX_C = vVALUE
    )
    WHEN MATCHED THEN
        UPDATE SET
            T.B = CASE 
                        WHEN SUBSTR(T.C,4,2) = C_INFO.MAX_C THEN TO_CHAR(TO_NUMBER(T.B) +1)
                        WHEN SUBSTR(T.C,4,2) = C_INFO.MIN_C THEN T.B
                        WHEN C_INFO.MAX_C = C_INFO.MIN_C THEN T.B
                        
                     END,
            T.A = CASE 
                          WHEN SUBSTR(T.C,4,2) = C_INFO.MIN_C THEN 2
                          WHEN SUBSTR(T.C,4,2) = C_INFO.MAX_C THEN 1 
                          WHEN C_INFO.MAX_C = C_INFO.MIN_C THEN 1
                      END;