Writing SQL UPDATE Queries with Arel
Arel is a relational algebra library in Ruby. It powers Rails's ActiveRecord query interface and can be used to build complex SQL programatically, without the need for error-prone string manipulation.
We can use Arel outside of Rails models to build and execute a simple SQL UPDATE statement.
Assume we have a database table, users, with a column name that we'd like to update for the user with an ID of 42.
Here we use the Arel::UpdateManager class, which knows how to build and manipulate SQL UPDATE queries:
table = Arel::Table.new(:users)
update_manager = Arel::UpdateManager.new table.engine
update_manager.set [[table[:id], 42], [table[:name], "Jane Smith"]]
ActiveRecord::Base.connection.execute update_manager.to_sqlNotice we use the table object to refer to columns of the users table.
This can be especially useful when writing Rake tasks to manipulate data, as the SQL that Arel produces is more likely to be compatible with your database than strings of SQL produced by hand.
Written by James Martin
Related protips
4 Responses
You have forgotten one line:
update_manager.table(table)
Furhtermore, it even doesn't do what was described.
The right syntax should be:
update_manager.set([[table[:name], "Jane Smith"]]).where(table[:id].eq(42)).table(table)
Not sure what Arel is about - it has overcomplicated and non-standard syntax. Active Record is a perfect fit.
@jirhradil: isn't active record using arel?
Have a fresh tip? Share with Coderwall community!
