티스토리 뷰
ORACLE ROWID를 이용한 중복데이터 삭제
1. 데이터 샘플
👉 총 데이터 20 ROW
👉 중복 데이터 16 ROW (삭제 대상)
👉 유니크 데이터 4 ROW
WITH DUP_TABLE AS (
-- 중복데이터 5개
SELECT 'DUP1_COLUMN1' AS COLUMN1, 'DUP1_COLUMN2' AS COLUMN2, 'DUP1_COLUMN3' AS COLUMN3, 'DUP1_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
SELECT 'DUP1_COLUMN1' AS COLUMN1, 'DUP1_COLUMN2' AS COLUMN2, 'DUP1_COLUMN3' AS COLUMN3, 'DUP1_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
SELECT 'DUP1_COLUMN1' AS COLUMN1, 'DUP1_COLUMN2' AS COLUMN2, 'DUP1_COLUMN3' AS COLUMN3, 'DUP1_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
SELECT 'DUP1_COLUMN1' AS COLUMN1, 'DUP1_COLUMN2' AS COLUMN2, 'DUP1_COLUMN3' AS COLUMN3, 'DUP1_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
SELECT 'DUP1_COLUMN1' AS COLUMN1, 'DUP1_COLUMN2' AS COLUMN2, 'DUP1_COLUMN3' AS COLUMN3, 'DUP1_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
-- 중복데이터 5개
SELECT 'DUP2_COLUMN1' AS COLUMN1, 'DUP2_COLUMN2' AS COLUMN2, 'DUP2_COLUMN3' AS COLUMN3, 'DUP2_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
SELECT 'DUP2_COLUMN1' AS COLUMN1, 'DUP2_COLUMN2' AS COLUMN2, 'DUP2_COLUMN3' AS COLUMN3, 'DUP2_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
SELECT 'DUP2_COLUMN1' AS COLUMN1, 'DUP2_COLUMN2' AS COLUMN2, 'DUP2_COLUMN3' AS COLUMN3, 'DUP2_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
SELECT 'DUP2_COLUMN1' AS COLUMN1, 'DUP2_COLUMN2' AS COLUMN2, 'DUP2_COLUMN3' AS COLUMN3, 'DUP2_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
SELECT 'DUP2_COLUMN1' AS COLUMN1, 'DUP2_COLUMN2' AS COLUMN2, 'DUP2_COLUMN3' AS COLUMN3, 'DUP2_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
-- 중복데이터 5개
SELECT 'DUP3_COLUMN1' AS COLUMN1, 'DUP3_COLUMN2' AS COLUMN2, 'DUP3_COLUMN3' AS COLUMN3, 'DUP3_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
SELECT 'DUP3_COLUMN1' AS COLUMN1, 'DUP3_COLUMN2' AS COLUMN2, 'DUP3_COLUMN3' AS COLUMN3, 'DUP3_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
SELECT 'DUP3_COLUMN1' AS COLUMN1, 'DUP3_COLUMN2' AS COLUMN2, 'DUP3_COLUMN3' AS COLUMN3, 'DUP3_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
SELECT 'DUP3_COLUMN1' AS COLUMN1, 'DUP3_COLUMN2' AS COLUMN2, 'DUP3_COLUMN3' AS COLUMN3, 'DUP3_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
SELECT 'DUP3_COLUMN1' AS COLUMN1, 'DUP3_COLUMN2' AS COLUMN2, 'DUP3_COLUMN3' AS COLUMN3, 'DUP3_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
-- 중복데이터 5개
SELECT 'DUP4_COLUMN1' AS COLUMN1, 'DUP4_COLUMN2' AS COLUMN2, 'DUP4_COLUMN3' AS COLUMN3, 'DUP4_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
SELECT 'DUP4_COLUMN1' AS COLUMN1, 'DUP4_COLUMN2' AS COLUMN2, 'DUP4_COLUMN3' AS COLUMN3, 'DUP4_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
SELECT 'DUP4_COLUMN1' AS COLUMN1, 'DUP4_COLUMN2' AS COLUMN2, 'DUP4_COLUMN3' AS COLUMN3, 'DUP4_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
SELECT 'DUP4_COLUMN1' AS COLUMN1, 'DUP4_COLUMN2' AS COLUMN2, 'DUP4_COLUMN3' AS COLUMN3, 'DUP4_COLUMN4' AS COLUMN4 FROM DUAL UNION ALL
SELECT 'DUP4_COLUMN1' AS COLUMN1, 'DUP4_COLUMN2' AS COLUMN2, 'DUP4_COLUMN3' AS COLUMN3, 'DUP4_COLUMN4' AS COLUMN4 FROM DUAL
)
2. ORACLE ROWID를 이용한 중복데이터 조회
👉 먼저 생성된 ROWID를 기준으로 데이터를 기준으로 조회
👉 나중에 생성된 데이터를 남기고 싶다면 MAX(ROWID)
WITH DUP_TABLE AS (
-- 중복데이터 5개
SELECT 'DUP1_COLUMN1' AS COLUMN1... FROM DUAL
)
SELECT COUNT(1)
FROM DUP_TABLE A
WHERE ROWID > (
SELECT MIN(ROWID)
FROM DUP_TABLE B
WHERE A.COLUMN1 = B.COLUMN1
AND A.COLUMN2 = B.COLUMN2
AND A.COLUMN3 = B.COLUMN3
AND A.COLUMN4 = B.COLUMN4
);
3. ORACLE ROWID를 이용한 중복데이터 삭제
👉 실행 되지는 않음(WITH TABLE 은 삭제할 수 없음)
WITH DUP_TABLE AS (
-- 중복데이터 5개
SELECT 'DUP1_COLUMN1' AS COLUMN1... FROM DUAL
)
DELETE FROM DUP_TABLE
WHERE ROWID > (
SELECT MIN(ROWID)
FROM DUP_TABLE B
WHERE A.COLUMN1 = B.COLUMN1
AND A.COLUMN2 = B.COLUMN2
AND A.COLUMN3 = B.COLUMN3
AND A.COLUMN4 = B.COLUMN4
);
'Database' 카테고리의 다른 글
ORACLE 테이블 컬럼 추가, 수정, 삭제, 컬럼명 변경 (0) | 2021.01.06 |
---|---|
MySQL 전체 테이블 대상 삭제, 생성, SELECT-INSERT, 권한 관련 쿼리 (0) | 2017.09.18 |
MySQL Dump& Import (0) | 2017.09.18 |
MySQL data export to csv query (0) | 2017.09.12 |
컬럼의 특정 문자열 치환 (0) | 2017.06.23 |
댓글