activerecord - How to execute a raw update sql with dynamic binding in rails

Question

I want to execute one update raw sql like below:

update table set f1=? where f2=? and f3=?

This SQL will be executed by ActiveRecord::Base.connection.execute, but I don't know how to pass the dynamic parameter values into the method.

Could someone give me any help on it?

Answer 1

It doesn't look like the Rails API exposes methods to do this generically. You could try accessing the underlying connection and using it's methods, e.g. for MySQL:

st = ActiveRecord::Base.connection.raw_connection.prepare("update table set f1=? where f2=? and f3=?")
st.execute(f1, f2, f3)
st.close

I'm not sure if there are other ramifications to doing this (connections left open, etc). I would trace the Rails code for a normal update to see what it's doing aside from the actual query.

Using prepared queries can save you a small amount of time in the database, but unless you're doing this a million times in a row, you'd probably be better off just building the update with normal Ruby substitution, e.g.

ActiveRecord::Base.connection.execute("update table set f1=#{ActiveRecord::Base.sanitize(f1)}")

or using ActiveRecord like the commenters said.