rails active record: update a table with join

Question

I'm working on a rails project. I have two tables storing data from disparate systems: tasks and projects.

With a condition, Projects knows Tasks through ref_id, but not in the opposite direction. so, once matches found from a join, I'd like to set task.project_id with the matched Projects table's id.

UPDATE FROM task AS t
LEFT JOIN projects as p
ON t.ref_id = p.ref_id
SET t.project_id = p.id

my question here is: how can I achieve this using active record in rails?

Answer 1

If you're using postgres you can run an update_all with a "join" from the model like:

Task.connection.update_sql("UPDATE tasks AS t SET project_id = p.id FROM projects AS p WHERE t.ref_id = p.ref_id")

Note: the update_sql call is deprecated for Rails 5 and should be switched to just update

Since you are sending sql directly you need to make sure you are using syntax appropriate for your database.