[Oracle] 중복된 데이터 중 1개만 남기고 지우기

1. 이슈

프로젝트 도중 오라클 DB 마이그레이션 중 중복된 데이터가 생성되는 경우가 존재했다.

이를 제거하기 위한 쿼리문을 찾아봤다.

2. 중복된 데이터를 삭제하는 쿼리문

중복된 데이터는 조건 없이 하나만 남기고 삭제

DELETE FROM 테이블명

 WHERE ROWID IN (

       SELECT ROWID FROM (

              SELECT * FROM (

                     SELECT ROW_NUMBER() OVER(PARTITION BY 중복컬럼명 ORDER BY 중복컬럼명) AS num

                       FROM 테이블명

                     )

        WHERE num > 1 

       )

     );

 

이 쿼리문을 해석하면

먼저 PARTITION BY를 통해 중복된 컬럼기준으로 그룹별로 나누고, ROW_NUMBER 함수를 이용해 순번을 매긴다.

 

ROW_NUMBER 함수란?

 - 각 PARTITION 내에서 ORDER BY절에 의해 정렬된 순서를 기준으로 고유한 값을 반환하는 함수

-  PARTITION BY는 선택, ORDER BY는 필수

SELECT * FROM (
      SELECT ROW_NUMBER() OVER(PARTITION BY 중복컬럼명 ORDER BY 중복컬럼명) AS num
          FROM 테이블명
       )

예를 들어 컬럼명이 user_code면 중복된 코드별로 순번이 매겨진 결과값이 나온다.

 

다음으로 조회된 순번이 2개 이상인 값의 ROWID를 추출한다.

 

ROWID 란?

- ROWID 란 오라클에서 '데이터 주소'를 의미

- 테이블 각 레코드(행)이 가지고 있는 고유의 주소(정확한 저장 위치)를 나타낸다.

 

 SELECT ROWID FROM (

              SELECT * FROM (

                     SELECT ROW_NUMBER() OVER(PARTITION BY 중복컬럼명 ORDER BY 중복컬럼명) AS num

                       FROM 테이블명

                     )

        WHERE num > 1 

       )

고유값이기 때문에 이를 1개만 남기고 삭제하면 끝이다.

 

3. 중복된 데이터 중 나중에 입력한 데이터를 삭제

추가적으로 나중에 입력한 데이터를 삭제할수도 있다.

DELETE FROM 테이블명 a

 WHERE ROWID > (SELECT MIN(ROWID) FROM 테이블명 b

                           WHERE b.중복컬럼명 = a.중복컬럼명);

 

참고

https://m.blog.naver.com/regenesis90/222206835812

[오라클/SQL] ROWID : 인덱스 관리를 위한 데이터 주소 조회 방법

http://www.acronet.kr/python/26531

프로그래밍 노트 - [Oracle] 중복된 데이터 삭제하기 (delete duplicated data row)

잘못된 내용이 있다면 지적부탁드립니다. 방문해주셔서 감사합니다.