CODEWITHSUNDEEP
sqlruby-on-railsactiverecordmigration
robinyapockets
robinyapockets

Reputation: 393

SQL Copy Data from one table to another during migration

I'm trying to move data from one table to another during a migration while adding the new columns. My user model had the column phone, and phone_permission, which I'd like to move to the gallery model (including its data). My code:

def self.up
  add_column :galleries, :phone, :string
  add_column :galleries, :phone_permission, :integer, default: 1

  execute "UPDATE galleries AS g, users AS u SET g.phone = u.phone WHERE u.id = g.user_id"
  execute "UPDATE galleries AS g, users AS u SET g.phone_permission = u.phone_permission WHERE u.id = g.user_id"

  remove_column :users, :phone
  remove_column :users, :phone_permission
end

Error: SQLite3::SQLException: near "AS": syntax error: UPDATE galleries AS g, users AS u SET g.phone = u.phone WHERE u.id = g.user_id

Thanks for your help!

Upvotes: 0

Views: 1464

Answers (1)

born4new
born4new

Reputation: 1687

Your code above is not valid SQL for SQLite.

Also, why not do it using ActiveRecord? Something like this should do the trick:

Gallery.joins(:user).update_all('galleries.phone = users.phone')
Gallery.joins(:user).update_all('galleries.phone_permission = users.phone_permission')

EDIT: As the docs states for update_all

This method constructs a single SQL UPDATE statement and sends it straight to the database. It does not instantiate the involved models and it does not trigger Active Record callbacks or validations.

This is just a prettier way to write Raw SQL in my opinion.

EDIT 2: As @llya pointed out in the comments, your migration would break if you were to change your model name. A simple trick here is to add this class definition on top of the migration:

class Gallery < ActiveRecord::Base; end

Upvotes: 1

Related Questions