You will need this SQL statement to list all duplicate contents in a table
SELECT email,
COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 ) |
SELECT email,
COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )
source
If you want to list all duplicate records
SELECT t1.* FROM
`some_table` t1,
( SELECT documentno, COUNT(1) AS dup
FROM `some_table`
GROUP BY documentno
HAVING dup > 1 ) AS t2
WHERE t1.documentno = t2.documentno; |
SELECT t1.* FROM
`some_table` t1,
( SELECT documentno, count(1) AS dup
FROM `some_table`
GROUP BY documentno
HAVING dup > 1 ) AS t2
WHERE t1.documentno = t2.documentno;
Then how to remove the duplicate records?
1. Create new table and insert clean records (without duplicate)
CREATE TABLE new_table AS
SELECT * FROM old_table WHERE 1 GROUP BY [COLUMN TO remove duplicates BY]; |
CREATE TABLE new_table as
SELECT * FROM old_table WHERE 1 GROUP BY [column to remove duplicates by];
Note: Be careful, this will create a table without primary key, auto-increment etc. So make sure you update your table after run this statement
2. Drop old table (with duplicates)
source