Recently I’ve been working on a Rails app that allows users to post large batches of data. New records are inserted and existing records are updated. Since there could be hundreds of records in a single post, I decided to try Zach Dennis’ activerecord-import gem and I was very pleased with the results. By importing with columns and arrays of values and turning off model validations I was seeing huge performance gains. I am using a MySQL database so I made use of the on_duplicate_key option to update a parameters value if an existing parameter was sent.

The only problem was that there is a functional requirement that once a parameter is created by a provider, only that provider can update the value. Looking at the activerecord-import wiki I couldn’t find a way to conditionally update values on duplicate key. I really didn’t want to use model validations because I needed this import to be as fast as possible, so I decided to take a look at the source code and hope to find an undocumented gift.

Huzzah! Besides an array or hash, on_duplicate_key_update accepts a string of SQL!

Awesome, now my import is fast and I can even do conditional updates without using validations.