소스: sql-src/work_05_dedup/02_delete_keep_one.sql, 03_dialects.sql.
삭제 전에 백업 테이블을 만듭니다.
CREATE TABLE member_bak AS SELECT * FROM member;CREATE TABLE ... AS SELECT 는 Oracle 과 MySQL 이 같고, MSSQL 은 SELECT * INTO member_bak FROM member 입니다. H2 에서는 위 형태가 실행됩니다. 다음은 삭제 대상을 SELECT 로 먼저 봅니다. 키는 이름·전화·이메일이고 그룹마다 가장 작은 id 를 남깁니다.
SELECT
id
, name
, phone
, email
FROM member
WHERE id NOT IN (SELECT MIN(id) FROM member GROUP BY name, phone, email)
ORDER BY id;ID | NAME | PHONE | EMAIL
---+--------+---------------+----------
2 | 김대표 | 010-1111-2222 | [email protected]
14 | 남총무 | 010-5656-7878 | NULL
(2행)대상이 2건이고 예상과 일치하므로 같은 WHERE 조건으로 삭제합니다.
DELETE FROM member WHERE id NOT IN (SELECT MIN(id) FROM member GROUP BY name, phone, email);삭제된 행은 2건이고, 남긴 12행에서 3번(이메일 앞 공백)과 5번(대문자)은 그대로입니다. 완전 중복만 지웠기 때문입니다. 남총무는 NULL 이메일 그룹이 GROUP BY 에서 하나로 묶여 14번이 지워졌습니다. 자기 조인의 = 로 했다면 잡히지 않았을 행입니다.
NOT IN 서브쿼리가 NULL 을 돌려주면 0건이 되는 함정은 소스 4번 구간에 있습니다. WHERE name NOT IN (SELECT email FROM member WHERE id = 7) 처럼 NULL 이메일을 고르면 결과가 0이 됩니다. MIN(id) 는 NULL 이 아니라 안전하지만 키 컬럼을 그대로 고르는 삭제는 이 함정에 걸립니다(중급 02 EXISTS·IN 과 NULL 함정).
이번에는 전화번호 정규화 키가 같은 회원 중 가장 최근에 가입한 행을 남깁니다. 먼저 삭제 대상을 봅니다.
SELECT
id
, name
, phone
, joined_at
, rn
FROM (
SELECT
id
, name
, phone
, joined_at
, ROW_NUMBER() OVER (PARTITION BY REPLACE(phone, '-', '')
ORDER BY joined_at DESC, id DESC) AS rn
FROM member
) t
WHERE rn > 1
ORDER BY id;ID | NAME | PHONE | JOINED_AT | RN
---+----------+---------------+------------+---
1 | 김대표 | 010-1111-2222 | 2024-01-10 | 2
4 | 이개발 | 010-3333-4444 | 2024-02-01 | 2
9 | 한디자인 | 010-1212-3434 | 2024-04-01 | 2
(3행)joined_at DESC 만 쓰면 가입일이 같은 행 사이에 순서가 정해지지 않으므로 id DESC 를 함께 둡니다. 그러면 결과가 항상 같습니다. 삭제 대상 id 를 뽑아 DELETE 에 넘깁니다.
DELETE FROM member
WHERE id IN (
SELECT id
FROM (
SELECT
id
, ROW_NUMBER() OVER (PARTITION BY REPLACE(phone, '-', '')
ORDER BY joined_at DESC, id DESC) AS rn
FROM member
) t
WHERE rn > 1
);윈도 함수는 DELETE 의 WHERE 에 직접 쓸 수 없어서 인라인 뷰에서 rn 을 만든 뒤 id 만 넘깁니다. 3건이 지워지고 12행이 9행이 됩니다. 남은 행은 3, 5, 6, 7, 8, 10, 11, 12, 13 번입니다. 남은 3번은 이메일 앞 공백이 그대로이므로 값을 정규화하는 UPDATE 가 뒤따라야 합니다.
백업의 14건에서 5건이 빠져 9행이 됐습니다. 예제 6 의 2건과 이번 3건을 더한 값이라 예상과 맞습니다(소스 7번 구간).
정리하기 전에 UNIQUE 를 걸면 실패합니다. 정리 전 14행 상태에서 전화번호에 제약을 겁니다.
-- @error
ALTER TABLE member ADD CONSTRAINT uq_member_phone UNIQUE (phone);예상 오류: Unique index or primary key violation: "PUBLIC.UQ_MEMBER_PHONE_INDEX_8 ON PUBLIC.MEMBER(PHONE NULLS FIRST) VALUES ( /* 1 */ '010-1111-2222' )"같은 전화번호가 이미 두 건이라 제약이 만들어지지 않았습니다. 실제 DB 의 오류 메시지는 다르지만 중복이 남아 있으면 실패하는 점은 같습니다. 정리는 세 문장입니다. 완전 중복 삭제, 값 정규화, 사실상 중복 삭제 순입니다.
DELETE FROM member WHERE id NOT IN (SELECT MIN(id) FROM member GROUP BY name, phone, email);
UPDATE member SET phone = REPLACE(phone, '-', ''), email = LOWER(TRIM(email));
DELETE FROM member WHERE id NOT IN (SELECT MAX(id) FROM member GROUP BY phone);남는 행은 3, 5, 6, 7, 8, 10, 11, 12, 13 번 9행이고 전화번호는 하이픈이 없는 모양, 이메일은 소문자입니다.
정규화를 먼저 하면 사실상 중복이 완전 중복이 되어 GROUP BY 한 번으로 지워집니다. 값을 저장할 때부터 같은 모양으로 맞추는 것이 가장 단순한 재발 방지입니다. 이제 UNIQUE 가 걸립니다.
ALTER TABLE member ADD CONSTRAINT uq_member_phone UNIQUE (phone);
ALTER TABLE member ADD CONSTRAINT uq_member_email UNIQUE (email);이메일 UNIQUE 는 NULL 4건이 있어도 통과합니다. Oracle 과 MySQL 은 UNIQUE 컬럼에 NULL 을 여러 개 허용하고 MSSQL 은 NULL 하나만 허용합니다. 이 사실은 H2 로 재현하지 않으며 문법은 다음 예제에서 봅니다. 마지막으로 이미 있는 회원을 다시 넣어 봅니다.
-- @error
INSERT INTO member VALUES (15, '김대표', '01011112222', '[email protected]', DATE '2024-07-01');예상 오류: Unique index or primary key violation: "PUBLIC.UQ_MEMBER_PHONE_INDEX_8 ON PUBLIC.MEMBER(PHONE NULLS FIRST) VALUES ( /* 3 */ '01011112222' )"소스 03_dialects.sql 은 SET MODE 별로 실행되는 형태만 돌립니다. 표준 NOT IN 삭제와 CREATE TABLE ... AS SELECT 는 Oracle 모드와 MySQL 모드에서도 실행됩니다. 아래 관용구는 H2 미지원이라 문법 검토만 합니다.
-- Oracle · Tibero (H2 미지원, 문법 검토만)
DELETE FROM member
WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM member GROUP BY name, phone, email);Oracle ROWID 는 행의 물리 주소라서 PK 가 없는 테이블에서도 중복 행을 구별합니다. Tibero 에도 ROWID 가 있습니다. 완전히 같은 행이 있어도 ROWID 는 서로 다릅니다.
-- MSSQL (H2 미지원, 문법 검토만)
WITH c AS (
SELECT
id
, ROW_NUMBER() OVER (PARTITION BY name, phone, email ORDER BY id) AS rn
FROM member
)
DELETE FROM c WHERE rn > 1;MSSQL 은 CTE 를 대상으로 DELETE 할 수 있고, 단일 테이블 기반이면 원본 행이 지워집니다. 앞 문장이 있으면 ; 로 끝내야 합니다. 이 형태는 예제 7 의 ROW_NUMBER 와 같은 발상입니다.
-- MySQL (H2 미지원, 문법 검토만)
DELETE m1
FROM member m1
JOIN member m2 ON m1.name = m2.name
AND m1.phone = m2.phone
AND m1.email <=> m2.email
AND m1.id > m2.id;MySQL 에서 삭제 대상 테이블을 같은 문장의 서브쿼리 FROM 에 직접 쓰면 ERROR 1093 이 납니다. 자기 조인 DELETE 로 쓰거나 서브쿼리를 파생 테이블로 한 번 감쌉니다. 이 오류는 H2 에서 재현 안 됨이고, 소스 03 의 MySQL 모드 구간은 NOT IN (SELECT id FROM (SELECT MIN(id) AS id ...) t) 로 감싼 형태를 실행만 확인한 것입니다.
자기 조인에 쓴 <=> 는 NULL 도 같다고 보는 MySQL 전용 비교입니다. 일반 = 로 쓰면 NULL 이메일 행은 짝이 되지 않아 지워지지 않습니다(예제 5 와 같은 이유).
정규화 키로 UNIQUE 를 걸려면 DB 마다 방법이 다릅니다. 아래도 H2 미지원 문법 검토용입니다.
| DB | 방법 |
|---|---|
| Oracle | 함수 기반 유니크 인덱스 |
| MySQL 8.0.13 부터 | 함수 인덱스 ((식)) |
| MSSQL | 계산 열 + 유니크 인덱스 |
-- Oracle · Tibero
CREATE UNIQUE INDEX uq_member_email_key ON member (LOWER(TRIM(email)));
-- MySQL 8.0.13 이상
CREATE UNIQUE INDEX uq_member_email_key ON member ((LOWER(TRIM(email))));
-- MSSQL: NULL 하나만 허용하므로 필터 인덱스로 NULL 제외
ALTER TABLE member ADD email_key AS LOWER(LTRIM(RTRIM(email)));
CREATE UNIQUE INDEX uq_member_email_key ON member (email_key) WHERE email IS NOT NULL;H2 에서는 표준 생성 열로 같은 효과를 실행해 볼 수 있습니다. 소스 마지막 구간이 이 방식이고 두 문장이 오류 없이 통과합니다.
ALTER TABLE member ADD COLUMN email_key VARCHAR(40) GENERATED ALWAYS AS (LOWER(TRIM(email)));
ALTER TABLE member ADD CONSTRAINT uq_member_email_key UNIQUE (email_key);주의MSSQL 의 UNIQUE 는 NULL 을 하나만 허용합니다. 선택 입력 컬럼에 UNIQUE 를 걸 때는
WHERE 컬럼 IS NOT NULL필터 인덱스를 씁니다.