Rename each entry (or row) of a column in a rails migration

Question

I want to remap values in my status column based on a hash.

For example if the status is a, change it to b.

How do I do this in Rails and optimize it so that it executes one db call that changes all values in the column?

score 3 · Answer 1 · answered Sep 28 '12 at 19:10

3

status_changes = { "a" => "b", "c" => "d" }

status_changes.each do |old, new|
  # first arg is UPDATE, second arg is WHERE
  SomeModel.update_all("status = #{new}", "status = #{old}")
end

answered Sep 28 '12 at 19:10

Tom L

3,389
1
16
14

This is great, but I think won't scale well if I have more mappings, since this executes a db query in each iteration. – n00shie Oct 04 '12 at 21:33

score 0 · Answer 2 · answered Sep 28 '12 at 19:07

0

If the hash is small compared to the table, you can do a SQL UPDATE ... WHERE ... for each entry in the hash, wrapped into a transaction.

answered Sep 28 '12 at 19:07

rewritten

16,280
2
47
50

This is kind of what I am looking for. My hash is just 3 entries. I am guessing that I should use Active Record's execute method to execute the full SQL query. However, if I want to add parameters in the query like: ActiveRecord::Base.connection.execute(Update ? where status = ?, param_a, param_b) Can I do that? – n00shie Sep 28 '12 at 21:29
TomL's response is more explicit than mine, you look at the docmentation to #update_all and you get the answer (which is basically that you call #update_all on a scope) – rewritten Sep 28 '12 at 23:48

score 0 · Accepted Answer · answered Oct 04 '12 at 21:30

I did this instead, using heredocs for better readability:

status_mapping = {"Open" => 1345, "Closed" => 1346, "Pending" => 1347}
query = ActiveRecord::Base.connection()
    query.execute <<-SQL.strip_heredoc
      UPDATE table_1 
      SET status = CASE 
        WHEN status = #{status_mapping["Open"]} THEN 0
        WHEN status = #{status_mapping["Closed"]} THEN 1 
        WHEN status = #{status_mapping["Pending"]} THEN 2 
        ELSE NULL
      END;
    SQL

Rename each entry (or row) of a column in a rails migration

3 Answers3