Doing an update_all with joins in Rails

"UPDATE FROM" is not standard SQL, so it's not surprising if it's not directly supported by Active Record. However, Active Record does give you a way to bypass its abstractions and just issue straight SQL, for those times when you've got to do something it doesn't support. Inside a model:

sql = "UPDATE FROM tasks AS t LEFT JOIN projects as p ON t.project_id = p.id SET t.invoice_id = 7WHERE p.organization_id == 42AND t.invoice_id IS NULL"connection.update_sql(sql)

ActiveRecord::Base also has a "select_by_sql" method that lets your non-standard select statements return regular active record model instances.

I believe at least @ rails 3.0.8 and ARel 2.0.10, we couldn't directly generate UPDATE FROM, but it is possible to get the same result with by resolving the join as a sub-query, e.g.

Task.where(:invoice_id=>nil).where(:project_id=>Project.where(:organization_id=>42).collect(&:id)).update_all(:invoice_id => 7)

This generates SQL like:

UPDATE "tasks"SET "invoice_id" = 7 WHERE "invoice_id" IS NULL AND "project_id" IN (1,2,3);-- assuming projects 1,2,3 have organization_id = 42

Of ourse, that's resolving the sub-query in Rails not in SQL. To generate a sub-select in SQL, you can mix in a little Arel like this:

t = Task.arel_tablep = Project.arel_tableTask.where(:invoice_id=>nil).where(  t[:project_id].in(    p.where(      p[:organization_id].eq(42)    ).project(p[:id])  )).update_all(:invoice_id => 7)

Which generates sql like this:

UPDATE "tasks"SET "invoice_id" = 7 WHERE "invoice_id" IS NULL AND "project_id" IN (SELECT "projects"."id" FROM "projects" WHERE "projects"."organization_id" = 42);

There's a pure ARel way of doing this, but the UpdateManager syntax is woefully under-documented

This is purely ruby/rails, it should not be tagged as SQL --

The only SQL info you could get is : start from another syntactic equivalent instead of the "update from" which is not standard, like for example this (which I wouldn't do either but hey I don't use ruby/rails).

UPDATE tasks tSET t.invoice_id=7 WHERE t.invoice_id IS NULL AND (SELECT p.organization_id FROM tasks t2 LEFT JOIN projects p ON t.project_id=p.id WHERE t2.id=t.id)=42