[SQL]중복된 ROW만 골라서 삭제
중복된 로우만 골라서 삭제하라.
배경
프로그램 개발을 위하여 샘플 데이터를 로딩한 뒤
PRIMARY KEY나 UNIQUE 인덱스를 만들고 싶지만
원시 데이터의 잘못으로 중복된 로우가 있으면 에러가 발생한다.
이럴 경우에 이를 확인하고 삭제하고 싶을 때가 있다.
EMP 테이블을 예를 들어 설명한다.
SELECT ROWID, ROWNUM, EMPNO, ENAME
FROM EMP
ORDER BY EMPNO, ROWID;
ROWID ROWNUM EMPNO ENAME
--------------------- -------- --------- ----------
00024F04.0002.0005 3 7698 BLAKE
00024F04.0005.0005 4 7698 BLAKE ←삭제 대상
00024F04.0006.0005 5 7782 CLARK
00024F04.0001.0005 2 7788 SCOTT
00024F04.0007.0005 6 7788 SCOTT ←삭제 대상
00024F04.000B.0005 10 7788 SCOTT ←삭제 대상
00024F04.0008.0005 7 7839 KING
00024F04.0000.0005 1 7844 TURNER
00024F04.0009.0005 8 7844 TURNER ←삭제 대상
00024F04.000A.0005 9 7876 ADAMS
10 rows selected.
요구 내용
위의 데이터 중 EMPNO 컬럼의 값이 중복된 로우에 대하여 ROWID 값이 큰 로우만 골라서 삭제하라.
해답
중복된 로우를 찾아보기만 하는 경우
SELECT EMPNO, ENAME FROM EMP A
WHERE ROWID > ( SELECT MIN(ROWID) FROM EMP B
WHERE A.EMPNO = B.EMPNO );
중복된 로우를 찾아서 ROWID가 더 큰 것을 삭제하는 경우
이 때 ROWID가 더 큰 것은 아마도 뒤에 생긴 데이터일 가능성이 더 많기 때문이다.
DELETE FROM EMP A
WHERE ROWID > ( SELECT MIN(ROWID) FROM EMP B
WHERE A.EMPNO = B.EMPNO );
만약 나중에 들어온 데이터를 살리고 먼저 들어온 데이터를 삭제하고자 한다면
WHERE 조건의 부등호와 MIN을 MAX로 바꾸어서 다음과 같이 하면 된다.
DELETE FROM EMP A
WHERE ROWID < ( SELECT MAX(ROWID) FROM EMP B
WHERE A.EMPNO = B.EMPNO );
