Deleting Duplicate Database Rows

By , 13 July 2008

Deleting Duplicate Database Rows

Coming up with a query to find duplicate rows in a database is not as easy as it should be and always turns out dog slow. Here's a simple alternative which I used when I had to do just that:

  • Dump the database.
  • Delete all the records.
  • Add a UNIQUE constraint on the natural key.
  • Reload the records from the database dump.

If you have duplicates in the dump, the INSERT will fail and they won't make it back into the database.

Easy or WHAT?!

Deleting Duplicate Database Rows
 

About Roger Keays

Deleting Duplicate Database Rows

Roger Keays is an artist, an engineer, and a student of life. Since he left Australia in 2009, he has been living as a digital nomad in over 40 different countries around the world. Roger is addicted to surfing. His other interests are music, psychology, languages, and finding good food.

Leave a Comment

Please visit https://rogerkeays.com/blog/deleting-duplicate-database-rows to add your comments.