Rails migration to update database table_one with data from related table

Question

I have two tables. Table_1 has a 1 to many relationship with table_2. My goal is to move all the data in table_2 to a newly created text column in table_1, using a rails migration. I am to do the following in a migration using sqlite.

def up
 sql = "UPDATE table_1 SET column=(SELECT name || ' ' || email FROM       
 table_2 WHERE table2.table_1_id=table_1.id)"
 update (sql)
end

This works, but the problem is, each record in table_1 has 1 or more related records in table_2, how do I set the field in table_1 to all related records in table_2

score 1 · Answer 1 · edited May 23 '17 at 12:12

1

I would write a custom seed file and run it via a rake task for data tasks such as these, with an implemention such as that suggested in the answer here:

Adding a custom seed file

As it's a rb file you can use standard model/loops/active record Rails code which should make it simpler to maintain associations and the like.

edited May 23 '17 at 12:12

Community

1
1

answered Nov 08 '14 at 16:52

RichardAE

2,945
1
17
20

score 1 · Answer 2 · answered Nov 12 '14 at 10:10

The best option is in your migration, apart from the code to create the new table, add code to "import" the data from the old tables into the new one. After that if the old tables are not relevant you can delete them.

  def up
    add_column :table1, :newfield, :string

    Table1.all.each do |record|
      record.newfield = record.relation.fields
      record.save!
  end

# Beware that if you need to rollback, it's better to implement a way 
# to revert the changes.
end

Rails migration to update database table_one with data from related table

2 Answers2