중복행 제거하기 > db

본문 바로가기

db

중복행 제거하기

페이지 정보

작성자 서방님 댓글 0건 조회 25회 작성일 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)
INSERT INTO Test_dup VALUES(2, 20)
INSERT INTO Test_dup VALUES(3, 30)
INSERT INTO Test_dup VALUES(4, 40)
INSERT INTO Test_dup VALUES(4, 40)
INSERT INTO Test_dup VALUES(5, 50)
GO

위와 같이 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)

중복행을 제거하는 방법은 해당 테이블의 구조에 따라 방법이 달라야 하는 경우도 있습니다. 위와 같이 단순하지 않을 수 있음을 기억하시기 바랍니다. 되도록이면 처음 테이블을 설계할 때 심사숙고해서 잘못된 데이터가 들어가지 않도록 해야합니다.

댓글목록

등록된 댓글이 없습니다.

Total 447건 1 페이지
게시물 검색

회원로그인

접속자집계

오늘
224
어제
282
최대
592
전체
37,322

그누보드5
Copyright © seobangnim.com All rights reserved.
자바스크립트를 활성화 하세요![ 브라우저에서 자바스크립트를 활성화하는 방법 ]