I think, therefore I blog

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. Click here to subscribe to his weekly blog, or stalk him on Facebook and Twitter.

Leave a Comment

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

Join Over 1000 Subscribers

I write every Sunday about travel, psychology, and technology. Thousands of people just like you have already subscribed—and for good reason. It'll change your life. And it's free :)

Read a Good Book

“A spellbinding true story of love, passion and adventure. One can’t help but be swept away by 100% Love Guaranteed.” —Dr Tammie Matson, author of Elephant Dance.

Chat For A While

Your Vote Matters

Which animal will take over when humans go extinct?