uscvva
Last Updated: February 25, 2016
·
4.755K
· jamesmartin

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_sql

Notice 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.

4 Responses
Add your response

3295

You have forgotten one line:
update_manager.table(table)

over 1 year ago ·
11236

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)

over 1 year ago ·
16262

Not sure what Arel is about - it has overcomplicated and non-standard syntax. Active Record is a perfect fit.

over 1 year ago ·
16291

@jirhradil: isn't active record using arel?

over 1 year ago ·