T-Tiger

사용 데이터

SELECT first_name, last_name, email, COUNT(*) as cnt
FROM contacts
GROUP BY email, first_name, last_name
HAVING COUNT(email) > 1 AND COUNT(first_name) > 1 AND COUNT(last_name) > 1;

삭제할 데이터 id 확인

SELECT t1.*
FROM contacts t1 JOIN contacts t2
ON t1.first_name=t2.first_name AND t1.last_name=t2.last_name AND t1.email=t2.email
WHERE t1.id > t2.id;

MySQL

데이터 삭제 쿼리

DELETE t1 FROM contacts t1 
JOIN contacts t2
ON t1.first_name=t2.first_name AND t1.last_name=t2.last_name AND t1.email=t2.email
WHERE t1.id > t2.id;

삭제할 데이터 id 확인

SELECT *
FROM ( SELECT id, first_name, last_name, email,
       ROW_NUMBER() OVER (PARTITION BY first_name, last_name, email) as row_num
       FROM contacts ) a
WHERE row_num > 1
ORDER BY first_name; -- 단순 정렬 용도

간단한 데이터 삭제 쿼리

DELETE FROM contacts
WHERE id IN (SELECT id
             FROM ( 
                 SELECT id, ROW_NUMBER() OVER (PARTITION BY first_name, last_name, email) as row_num
                 FROM contacts 
                 ) tmp
             WHERE row_num > 1);

데이터 삭제 쿼리

DELETE FROM contacts t1 
USING contacts t2
WHERE t1.first_name=t2.first_name AND t1.last_name=t2.last_name AND t1.email=t2.email AND t1.id > t2.id;