[ORACLE] 중복 데이터 삭제/제거하는 쿼리

중복데이터를 제거하기 위해 하나의 데이터만 남기고 모두 삭제하는 쿼리.

 

<중복 데이터 조회 쿼리>

https://kwomy.tistory.com/76

[ORACLE] 중복 데이터 조회 쿼리

 

* 중복된 데이터를 제거하기 위해 테이블의 PK를 이용한다.

* 테이블의 PK가 모호하거나 PK가 없는 로그성 같은 테이블일 경우, 오라클 함수 ROWID를 이용한다.

 

* ROWID란?

 - 테이블에 있는 해당 ROW를 찾기위해 ORACLE DB 내에서 사용되는 논리정보.

 - 해당 테이블에 유일값이 없는 경우 UNIQUE한 ROWID를 사용하여 유일성을 부여한다.

 

* 사용 쿼리

 - 삭제 대상의 ROWID를 구한 후 MAX값을 찾아내어, 구한 MAX( ROWID )값 보다 작은 것들을 조회->삭제

 - 테이블에서 도로명 주소 코드가 중복된 값들을 조회한 결과

SELECT R.* 
FROM (
	SELECT  RA.RN_CD,
		COUNT(*) OVER(PARTITION BY RA.RN_CD) AS CNT
	FROM ERSS.SM_ROADNAME_ADDR RA) R
WHERE R.CNT > 1;

 

 - 테이블에서 부여된 ROWID와 MAX( ROWID )를 조회

SELECT R.* 
FROM (
	SELECT  RA.RN_CD,
		COUNT(*) OVER(PARTITION BY RA.RN_CD) AS CNT,
		RA.ROWID AS RID,
		MAX(RA.ROWID) over (partition by RA.RN_CD) AS MAX_RID
	FROM ERSS.SM_ROADNAME_ADDR RA) R
WHERE R.CNT > 1
AND R.RID < R.MAX_RID;

 

중복된 컬럼에서 ROWID가 최대인 ROW를 제외한 나머지 ROW가 조회됨.

 

* ROW_NUMBER() 사용

SELECT R.* 
FROM (
	SELECT  RA.RN_CD,
		COUNT(*) OVER(PARTITION BY RA.RN_CD) AS CNT,
		RA.ROWID AS RID,
		ROW_NUMBER() OVER(PARTITION BY RA.RN_CD ORDER BY RA.ROWID DESC) AS RN
	FROM ERSS.SM_ROADNAME_ADDR RA) R
WHERE R.CNT > 1;

 

============================================================================

 - 위 조회 결과값들만 삭제 ( IN 함수 사용 ) 

 - DELETE 또는 UPDATE 쿼리 실행 전에는 꼭 SELECT로 원하는 결과가 나오는지 두번 세번 확인할 것 ! 

DELETE
FROM ERSS.SM_ROADNAME_ADDR R
WHERE R.ROWID IN (
		SELECT RA.RID
		FROM (
			SELECT  RA.ROWID AS RID,
            		ROW_NUMBER() OVER (PARTITION BY RA.KIKB_DONG_NM ORDER BY RA.ROWID DESC) AS RN 
            FROM ERSS.SM_ROADNAME_ADDR RA
			 ) RA
		WHERE RA.RN > 1
); 

============================================================================