중복행 제거하기
페이지 정보
작성자 서방님 댓글 0건 조회 263회 작성일 06-12-13 21:05본문
|
사용중이던 테이블에 UNIQUE 인덱스를 만들고자 하는 경우 기존에 중복된 값이 있으면 에러가 발생합니다. 이런 경우 중복된 값을 제거하는 간단한 방법을 소개해 드립니다. |
가끔 이런 질문을 받습니다.
"UNIQUE 인덱스를 만들려고 하는데 테이블에 이미 중복된 값이 있어 UNIQUE 인덱스를 만들 수 없네요. 어떻게 해야 하나요?"
팁이라고 하기에는 좀 부족한 점이 있기는 하지만 이와 관련된 질문이 종종 올라오는것 같아 간단히 정리하고자 합니다.
질문의 내용처럼 기존의 테이블에 UNIQUE 인덱스를 만들려고 하는데 존재하는 데이터중에 중복된 값이 있으면 UNIQUE 인덱스를 만들 수 없습니다. 예를 들면 다음과 같습니다.
|
|
CREATE TABLE Test_dup ( col1 int, col2 int ) GO INSERT INTO Test_dup VALUES(1, 10) |
위와 같이 Test_dup이라고 하는 테이블이 존재하고 여섯개의 행이 존재합니다. 이 상태에서 col1 컬럼을 대상으로 UNIQUE 인덱스를 생성하려고 한다면 다음과 같은 에러가 발생합니다.
|
|
서버: 메시지 1505, 수준 16, 상태 1, 줄 1 인덱스 ID 2에 중복 키가 있어 CREATE UNIQUE INDEX가 종료되었습니다. 가장 중요한 기본 키는 '4'입니다. 문이 종료되었습니다. |
위와 같은 에러가 발생하는 이유는 Test_dup 테이블의 col1 컬럼에는 동일한 값 4가 두번 존재하기 때문입니다. 중복된 col1 컬럼의 값을 확인하는 방법은 다음과 같습니다.
| SELECT col1 FROM Test_dup GROUP BY col1 HAVING Count(*) > 1 GO |
위와 같이 수행하면 4 라는 값이 표시됩니다. 이와 같은 상황에서 꼭 UNIQUE 인덱스를 만들어야 한다면 어떻게 해야 할까요?
가장 많이 사용되는 방법은 다음과 같이 중간 테이블을 만들어 중복되지 않은 데이터만을 추가한 후 기존의 테이블의 내용을 전부 지우고 중간 테이블의 내용을 다시 가져오는 방법입니다.
SELECT DISTINCT col1, col2 INTO #tmp01 FROM Test_dup -- 1) TRUNCATE TABLE Test_dup -- 2) INSERT INTO Test_dup SELECT * FROM #tmp01 -- 3) DROP TABLE #tmp01 -- 4) SELECT * FROM Test_dup -- 5) |
1) 에서는 #tmp01 이라는 이름의 임시 테이블에 DISTINCT 옵션을 이용해서 중복된 경우 한번만 검색이 되게 하여 그 결과를 기록했습니다.
2) 에서는 기존 테이블인 Test_dup 테이블의 내용을 모두 삭제했습니다.
3) 에서는 중복되지 않은 결과만을 갖고 있는 #tmp01 테이블의 모든 내용을 가져와 기존의 테이블(현재는 비워진 상태)인 Test_dup 테이블에 기록했습니다.
4) 중간에 만들어진 임시테이블 #tmp01은 이제 필요 없으므로 제거했습니다.(물론 임시 테이블이므로 나중에 자동으로 제거됩니다)
5) 에서와 같이 결과를 확인하면 중복된 행은 제거된 결과를 Test_dup 테이블이 갖고 있음을 알 수 있습니다.
위와 같은 작업의 결과로 Tesp_dup 테이블은 중복된 값이 더이상 없습니다. 그러므로 다음과 같이하여 UNIQUE 인덱스를 생성할 수 있습니다.
|
|
CREATE UNIQUE INDEX IDX01 ON Test_dup (col1) |
중복행을 제거하는 방법은 해당 테이블의 구조에 따라 방법이 달라야 하는 경우도 있습니다. 위와 같이 단순하지 않을 수 있음을 기억하시기 바랍니다. 되도록이면 처음 테이블을 설계할 때 심사숙고해서 잘못된 데이터가 들어가지 않도록 해야합니다.
댓글목록
등록된 댓글이 없습니다.
