Our staging database accidentally imported redundant records due to an ETL failure. I need to write production-grade SQL Queries to eliminate these duplicates while keeping the original row with the lowest unique identifier. What is the safest approach to do this without locking the tables?
3 answers
The safest and most professional strategy to clean up duplicate records involves using a Common Table Expression (CTE) combined with the ROW_NUMBER() window function. You partition your data by the duplicate columns and order them by your unique identifier. This assigns a sequential integer to each record within its group. From there, you can execute a target delete statement where the row number is greater than one. This ensures only extra records are removed while keeping the original entry intact.
Are you planning to run this cleanup script during active business hours, or do you have a dedicated maintenance window where table locks won't impact our live application users?
For smaller lookup tables, you can use a basic DELETE statement with a subquery that identifies the MIN(id) grouped by the duplicate values, deleting everything else.
Alice's solution is very straightforward and works beautifully for smaller transaction datasets where window functions might add unnecessary query compilation overhead.
If you must run it during active hours, you should break the deletion into smaller batches using a loop. Running massive SQL Queries all at once will escalate table locks and potentially freeze your application, whereas batching keeps operations completely unnoticeable.