대용량 테이블에서 중복된 행을 제거하고 각 그룹당 하나의 레코드만 유지하는 작업은 데이터 정제 과정에서 자주 발생하는 요구사항입니다. MySQL에서는 서브쿼리와 메인 쿼리가 동일한 테이블을 참조할 경우 제약이 있으므로, 이를 우회하는 방법이 필요합니다.

단일 필드 기준 중복 제거

예를 들어, user_id가 중복된 사용자 정보에서 가장 작은 ID를 가진 레코드만 보존하고 나머지는 삭제하고자 할 수 있습니다. 다음 쿼리는 이 목적을 달성하지만, 직접적인 서브쿼리 사용 시 오류가 발생할 수 있습니다.

DELETE FROM users 
WHERE user_id NOT IN (
    SELECT MIN(user_id) 
    FROM (
        SELECT MIN(user_id) AS user_id 
        FROM users 
        GROUP BY email
    ) AS temp
);

여기서 핵심은 중첩된 서브쿼리를 사용해 임시 테이블로 감싸는 것입니다. 이렇게 하면 MySQL이 동일 테이블에 대한 동시 읽기/쓰기 제약을 회피할 수 있습니다.

복합 필드 기준 중복 제거

두 개 이상의 컬럼 조합으로 중복을 판단해야 하는 경우도 있습니다. 예를 들어, name과 phone이 모두 같은 레코드를 중복으로 간주할 수 있습니다.

DELETE u1 FROM contacts u1
INNER JOIN contacts u2 
WHERE 
    u1.id > u2.id 
    AND u1.name = u2.name 
    AND u1.phone = u2.phone;

이 방식은 자기 조인(self-join)을 이용해 동일한 조건을 가진 레코드 중 ID가 더 큰(즉, 나중에 삽입된) 레코드를 삭제합니다. 성능 면에서도 인덱스가 있다면 효율적입니다.

임시 플래그를 통한 가상 삭제 (Soft Delete)

물리적 삭제 대신 상태 플래그를 업데이트하여 논리적으로 삭제 처리할 수도 있습니다.

UPDATE customer_log 
SET status = 'inactive'
WHERE log_id NOT IN (
    SELECT min_id FROM (
        SELECT MIN(log_id) AS min_id
        FROM customer_log
        GROUP BY customer_no, action_type
    ) AS temp_table
);

이 방법은 데이터 복구가 필요할 때 유리하며, 외래키 참조 무결성을 해치지 않도록 도와줍니다.

문자열 특정 위치 문자 제거

데이터 정제 중 특정 패턴의 문자를 제거해야 할 수도 있습니다. 예를 들어 칼럼 값의 첫 글자 또는 마지막 글자를 제외하는 작업입니다.

왼쪽 첫 글자 제거:

UPDATE product 
SET category = SUBSTRING(category, 2) 
WHERE category LIKE 'A%';

오른쪽 마지막 글자 제거:

UPDATE product 
SET category = LEFT(category, CHAR_LENGTH(category) - 1) 
WHERE category LIKE '%Z';

이러한 문자열 함수는 데이터 표준화 과정에서 유용하게 사용됩니다.