중복 데이터를 제거하는 sql query

중복 데이터를 제거하는 sql query

더시민 0 1353 0

SELECT ROW_NUMBER() OVER (PARTITION BY ENG_NAME ORDER BY ENG_NAME) AS NUM, * FROM NAME

 

eng_name 테이블을 기준으로 이름이 중복인 애들을 지워준다. 

 

DELETE A FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY ENG_NAME ORDER BY ENG_NAME) AS NUM, * FROM NAME ) A WHERE num >= 2

 

 

DELETE FROM 테이블명 WHERE 식별값 not in ( SELECT 식별값 from ( SELECT 식별값 from 테이블명 group by 중복되는 칼럼) as 식별값 )

예를 들어

DELETE FROM ask_match_category WHERE idx not in ( SELECT idx from ( SELECT idx from ask_match_category group by cin) as idx )

테이블명 : ask_match_category

식별값(고유값) : idx

중복되는 칼럼 : cin 으로 바꿔서 사용함

0 Comments
제목
Category
Facebook Twitter GooglePlus KakaoStory KakaoTalk NaverBand