[오라클] 중복행 제거

DELETE FROM TB_ZERO_CALL2

WHERE ROWID IN

(

    SELECT RID

    FROM

    (

        SELECT A.CONTRACTMST_NO

            , A.CONTRACTDETAIL_NO

            , COUNT(*) OVER(PARTITION BY A.CONTRACTMST_NO, A.CONTRACTDETAIL_NO) AS OVERLAP

            , A.ROWID AS RID

            , MAX(A.ROWID) OVER(PARTITION BY A.CONTRACTMST_NO, A.CONTRACTDETAIL_NO) AS MAX_RID

        FROM TB_ZERO_CALL2 A

        WHERE A.CLOSEEXP_DT = TO_DATE('20151013','YYYY-MM-DD')

    ) B

    WHERE B.OVERLAP > 1

    AND B.RID < MAX_RID

)

 

///////////////////////////////////////////////////////////////////////////

 

DELETE FROM TB_CONTRACT_KEY
WHERE ROWID IN (
    SELECT ROWID
    FROM
    (
        SELECT *
        FROM
        (
            SELECT ROW_NUMBER() OVER(PARTITION BY CONTRACTMST_NO, CONTRACTDETAIL_NO, KEY_TYPE, KEY_CODE ORDER BY CONTRACTMST_NO, CONTRACTDETAIL_NO, KEY_TYPE, KEY_CODE) AS NUM
            FROM TB_CONTRACT_KEY
            WHERE CONTRACTMST_NO = '13112173'
            AND CONTRACTDETAIL_NO = '4'
        )
        WHERE NUM > 1
    )
)