uscvva
Last Updated: February 25, 2016
·
4.024K
· jamesmartin
Me coding in regents park

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.

Say Thanks
Respond

4 Responses
Add your response

3295
92f1bdf5f7f139a3512da8b655abc80e

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

over 1 year ago ·
11236
0 gxkforb6fvh aplhxu9nopcwinvm2vth0ykzopfjppty1yn8amnik1pcd4zl7j82yyqjaqzuntmu

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
Cfd59846a3f2c8a93a714a75e5eeff3f

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

over 1 year ago ·
16291
820e01609a9f8f0fc0d5167680625b93

@jirhradil: isn't active record using arel?

over 1 year ago ·