중복 데이터를 제거하는 sql query
더시민
0
1353
0
2021.11.25 03:44
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 으로 바꿔서 사용함