SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;
Replace `columnname` with the name of the column(s) involved in the unique key constraint and `tablename` with the name of the table.
- To delete duplicates while keeping one instance:DELETE t1 FROM table_name t1
INNER JOIN table_name t2
WHERE
t1.id > t2.id AND
t1.columnname = t2.columnname;
- To update duplicates to make them unique (adjust the query based on your specific needs):UPDATE table_name
SET columnname = CONCAT(columnname, '_duplicate', id)
WHERE id IN (
SELECT id FROM (
SELECT id FROM table_name
WHERE column_name IN (
SELECT columnname FROM tablename
GROUP BY column_name
HAVING COUNT(*) > 1
)
) AS subquery
);
Note: The above update command is an example. You should tailor the SQL command based on your actual table structure and how you want to resolve duplicates.
6. Review your application logic or data entry processes to prevent this type of error in the future.
(Perfect for DevOps & SREs)
(Perfect for DevOps & SREs)
