How do you find and delete duplicate rows in SQL?
How do you find and delete duplicate rows in SQL?
First define "duplicate" (which columns) and "which row to keep" (usually the lowest id or the newest row).
SELECT email, COUNT(*) AS copies
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
To see every duplicate row with its position:
SELECT id, email, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM users;
PostgreSQL / MySQL 8 / SQL Server / Oracle (window function in a derived table or CTE):
DELETE FROM users
WHERE id IN (
SELECT id FROM (
SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM users
) t
WHERE rn > 1
);
The extra derived table avoids MySQL error 1093 ("cannot select from the target table"). SQL Server also allows deleting directly from a CTE (WITH d AS (SELECT ..., ROW_NUMBER() ...) DELETE FROM d WHERE rn > 1).
Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.