안녕하세요..사용 DBMS는 오라클 12 입니다..
WITH TBL AS ( SELECT 'L4' GRP, 'AAA' COD, 5 CD_SEQ, 1 DIS_SEQ, 'Y' VAL FROM DUAL UNION ALL SELECT 'L4' GRP, 'AAA' COD, 10 CD_SEQ, 2 DIS_SEQ, 'Y' VAL FROM DUAL UNION ALL SELECT 'L4' GRP, 'AAA' COD, 6 CD_SEQ, 9 DIS_SEQ, 'Y' VAL FROM DUAL UNION ALL SELECT 'L4' GRP, 'AAA' COD, 2 CD_SEQ, 9 DIS_SEQ, 'Y' VAL FROM DUAL UNION ALL SELECT 'L4' GRP, 'BBB' COD, 7 CD_SEQ, 1 DIS_SEQ, 'Y' VAL FROM DUAL UNION ALL SELECT 'L4' GRP, 'BBB' COD, 5 CD_SEQ, 1 DIS_SEQ, 'Y' VAL FROM DUAL UNION ALL SELECT 'L4' GRP, 'CCC' COD, 5 CD_SEQ, 1 DIS_SEQ, 'Y' VAL FROM DUAL UNION ALL SELECT 'L4' GRP, 'CCC' COD, 5 CD_SEQ, 2 DIS_SEQ, 'Y' VAL FROM DUAL UNION ALL SELECT 'L5' GRP, 'DDD' COD, 5 CD_SEQ, 1 DIS_SEQ, 'Y' VAL FROM DUAL UNION ALL SELECT 'L5' GRP, 'EEE' COD, 5 CD_SEQ, 1 DIS_SEQ, 'Y' VAL FROM DUAL UNION ALL SELECT 'L6' GRP, 'FFF' COD, 5 CD_SEQ, 1 DIS_SEQ, 'Y' VAL FROM DUAL UNION ALL SELECT 'L6' GRP, 'GGG' COD, 5 CD_SEQ, 1 DIS_SEQ, 'Y' VAL FROM DUAL ) SELECT TBL.* , ROW_NUMBER () OVER (PARTITION BY GRP, COD ORDER BY GRP, COD, DIS_SEQ, CD_SEQ) RN FROM TBL WHERE GRP = 'L4' ORDER BY GRP, COD, DIS_SEQ, CD_SEQ;
GRP | COD | CD_SEQ | DIS_SEQ | VAL | RN |
L4 | AAA | 5 | 1 | Y | 1 |
L4 | AAA | 10 | 2 | Y | 2 |
L4 | AAA | 2 | 9 | Y | 3 |
L4 | AAA | 6 | 9 | Y | 4 |
L4 | BBB | 5 | 1 | Y | 1 |
L4 | BBB | 7 | 1 | Y | 2 |
L4 | CCC | 5 | 1 | Y | 1 |
L4 | CCC | 5 | 2 | Y | 2 |
다음과 같은 데이터가 있을 경우 RN이 1인 경우만 VAL ='Y'로 업데이트 하고 아닌 경우는 N으로 업데이트 하려고 하는데요..
한방에 일괄 업데이트 하는 방법이 있을까요?
항상 도움 감사드립니다...^^
안녕하세요. 아래 링크 참고했습니다.
http://www.gurubee.net/article/18675
UPDATE TBL a SET VAL = (SELECT 'N' FROM (SELECT ROWID rid , ROW_NUMBER () OVER (PARTITION BY GRP, COD ORDER BY GRP, COD, DIS_SEQ, CD_SEQ) rn FROM TBL ) WHERE rid = a.ROWID ) WHERE ROWID IN (SELECT rid FROM (SELECT ROWID rid , ROW_NUMBER () OVER (PARTITION BY GRP, COD ORDER BY GRP, COD, DIS_SEQ, CD_SEQ) rn FROM TBL ) WHERE rn > 1 )