Update one column to value of another in Rails migration

83
votes

I have a table in a Rails app with hundreds of thousands of records, and they only have a created_at timestamp. I'm adding the ability to edit these records, so I want to add an updated_at timestamp to the table. In my migration to add the column, I want to update all rows to have the new updated_at match the old created_at, since that's the default for newly created rows in Rails. I could do a find(:all) and iterate through the records, but that would take hours because of the size of the table. What I really want to do is:

UPDATE table_name SET updated_at = created_at;

Is there a nicer way to do that in a Rails migration using ActiveRecord rather than executing raw SQL?

8
ruby-on-railsactiverecordmigrationtimestamp

8 Answers

145
votes

I would create a migration 

rails g migration set_updated_at_values

and inside it write something like:

class SetUpdatedAt < ActiveRecord::Migration
  def self.up
    Yourmodel.update_all("updated_at=created_at")
  end

  def self.down
  end
end

This way you achieve two things

  • this is a repeatable process, with each possible deploy (where needed) it is executed
  • this is efficient. I can't think of a more rubyesque solution (that is as efficient).

Note: you could also run raw sql inside a migration, if the query gets too hard to write using activerecord. Just write the following: 

Yourmodel.connection.execute("update your_models set ... <complicated query> ...")
21
votes

You can use update_all which works very similar to raw SQL. That's all options you have.

BTW personally I do not pay that much attention to migrations. Sometimes raw SQL is really best solution. Generally migrations code isn't reused. This is one time action so I don't bother about code purity.

16
votes

As gregdan wrote, you can use update_all. You can do something like this: 

Model.where(...).update_all('updated_at = created_at')

The first portion is your typical set of conditions. The last portion says how to make the assignments. This will yield an UPDATE statement, at least in Rails 4.

3
votes

You can directly run following command to your rails console ActiveRecord::Base.connection.execute("UPDATE TABLE_NAME SET COL2 = COL1")

For example: I want to update sku of my items table with remote_id of items tables. the command will be as following:
ActiveRecord::Base.connection.execute("UPDATE items SET sku = remote_id")

0
votes

This is a General way of solving, without the need for Writing Query, as queries are subjected to risk.

  class Demo < ActiveRecord::Migration
    def change
     add_column :events, :time_zone, :string
     Test.all.each do |p|
       p.update_attributes(time_zone: p.check.last.time_zone)
     end
     remove_column :sessions, :time_zone
    end
  end
0
votes

Do not use application models in migrations unless you redefine them inside migration. If you use application model tahat you later change or delete your migration might fail.

Of course you can also use full power of SQL inside migrations.

Read https://makandracards.com/makandra/15575-how-to-write-complex-migrations-in-rails

0
votes

You can also add updated_at column and update its values in one migration:

class AddUpdatedAtToTableName < ActiveRecord::Migration
  def change
    add_column :table_name, :updated_at, :datetime

    reversible do |dir|
      dir.up do
        update "UPDATE table_name SET updated_at=created_at"
      end
    end
  end
end
-4
votes

As a one time operation, I would just do it in the rails console. Will it really take hours? Maybe if there are millions of records…

records = ModelName.all; records do |r|; r.update_attributes(:updated_at => r.created_at); r.save!; end;`