Update one column to value of another in Rails migration
Ruby on-RailsActiverecordMigrationTimestampRuby on-Rails Problem Overview
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?
Ruby on-Rails Solutions
Solution 1 - Ruby on-Rails
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> ...")
Solution 2 - Ruby on-Rails
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.
Solution 3 - Ruby on-Rails
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.
Solution 4 - Ruby on-Rails
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")
Solution 5 - Ruby on-Rails
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
Solution 6 - Ruby on-Rails
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
Solution 7 - Ruby on-Rails
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
Solution 8 - Ruby on-Rails
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;`