CODEWITHSUNDEEP
ruby-on-railsactiverecord
Sarun Sermsuwan
Sarun Sermsuwan

Reputation: 3818

rails active record: update a table with join

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?

Upvotes: 8

Views: 6846

Answers (2)

vansan
vansan

Reputation: 762

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.

Upvotes: 1

shingara
shingara

Reputation: 46914

You can do that by passing your sql directly

Task.connection.update_sql("UPDATE FROM task AS t LEFT JOIN projects as p ON t.ref_id = #{p.ref_id} SET t.project_id = #{p.id}")

Upvotes: 0

Related Questions