Identify duplicate rows using `GROUP BY` and `HAVING COUNT(*) > 1`
Use a window function like `ROW_NUMBER()` to mark duplicates
Delete rows where `ROW_NUMBER() > 1`
Keep one row per duplicate group using a unique key or primary key
Use a CTE or subquery to target duplicate rows for deletion
Example:
`WITH cte AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY id) AS rn FROM table_name) DELETE FROM cte WHERE rn > 1;`
For databases that do not support deleting from a CTE, delete using a joined subquery
Example:
`DELETE FROM table_name WHERE id IN (SELECT id FROM (SELECT id, ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY id) AS rn FROM table_name) t WHERE rn > 1);`
Add a unique constraint after cleanup to prevent future duplicates
Test the delete query with `SELECT` first before running `DELETE`