# Database cleaning

**URL:** <https://www.ruby-forum.com/t/database-cleaning/155618>\
**Category:** Rails\
**Created:** [December 14, 2008, 7:25pm UTC](https://www.ruby-forum.com/t/database-cleaning/155618 "2008-12-14T19:25:49Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![indigoi](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/b3f665/32.png) [@indigoi](https://www.ruby-forum.com/u/indigoi)\
**Post date:** [December 14, 2008, 7:25pm UTC](https://www.ruby-forum.com/t/database-cleaning/155618/1 "2008-12-14T19:25:49Z")

</div>

Although i’m using validations in my models, i’ve managed to get a few  
duplicate entries on my site. I’ve got roughly 300,000 entries, and my  
administration only allows long running rake tasks of up to 5 hours. I  
need to remove all entries that have the same :word and :language.

Is there an easy way to iterate through all entries, check to see if any  
other entries have the same :word and :language, and delete those from  
the DB?

---

<div class="post-metadata">

**Author:** ![indigoi](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/b3f665/32.png) [@indigoi](https://www.ruby-forum.com/u/indigoi)\
**Post date:** [December 14, 2008, 8:04pm UTC](https://www.ruby-forum.com/t/database-cleaning/155618/2 "2008-12-14T20:04:29Z")

</div>

Richard S. wrote:

> Although i’m using validations in my models, i’ve managed to get a few  
> duplicate entries on my site. I’ve got roughly 300,000 entries, and my  
> administration only allows long running rake tasks of up to 5 hours. I  
> need to remove all entries that have the same :word and :language.
> 
> Is there an easy way to iterate through all entries, check to see if any  
> other entries have the same :word and :language, and delete those from  
> the DB?

Long term, I recommend adding a UNIQUE INDEX constraint to the database  
table (preferably via a migration) in addition to a  
validates\_uniqueness\_of in the model. The ActiveRecord validations are  
susceptible to race conditions, but the database constraint should not  
be.

Before you can add this index you’ll need to remove the duplicates.  
There are a couple ways to do this, and the best choice depends on how  
many dupes you have within the entire dataset. In most cases there are  
relatively few dupes (say less than 1000 in your 300K), and here’s my  
preference (performed in a rake task or a migration, or manually using  
the SQL tool of your choice, whichever way fits your workflow best):

1. Fetch the set of distinct tuples that are duplicated (word + language  
in your case). Something like this:

select word, language from entries group by word, language having  
count(\*) \> 1

1. Once you know what your duplicates are, iterate over them and delete  
all but one of the records:

dupes.each do |dupe|  
# this algorithm retains only the most recently added dupe, modify  
as needed  
# make sure you have an index on (word, language), not yet unique  
entries = Entry.all(  
:conditions =\> {  
:word =\> dupe.word,  
:language =\> dupe.language},  
:order =\> ‘id desc’)

```
entries.each_with_index do |e, index|
  e.destroy if index > 0
end

```

end

1. Re-run the query and confirm that you don’t have any lingering  
duplicates.

2. Add the UNIQUE INDEX constraint to prevent more dupes from being  
created. Make sure you have a validates\_uniqueness\_of in the model too.

Hope that helps!

Jeremy  
[http://jeronrails.blogspot.com](http://jeronrails.blogspot.com)