Migration update with multiple joined tables

rubyonrails-talk
1

I'm trying to do an update that requires table joins but I can't find any examples of updates beyond simple attribute value assignments. I have a select statement like this:

SELECT title, state, name FROM posts, zips, states WHERE posts.zip_id = zips.id AND states.abbreviation = zips.state;

The posts have a zip but not a state name yet. So I want to use an update like this:

UPDATE posts, zips, states SET posts.state_id = state.id WHERE posts.zip_id = zips.id AND states.abbreviation = zips.state;

I want to do this within a migration. I can't find any examples of how a migration like this should be handled. Does anyone know how to do this, or where I can find more complex updates than simple attribute value assignments?

Thanks, David

2

I found out about execute statements, but then I also learned that sqlite3 doesn't support this syntax. I'm not sure what to do. This seems like a common need.

3

def self.up     execute "UPDATE posts SET state_id = (SELECT states.id FROM states, zips WHERE posts.zip_id = zips.id AND states.abbreviation = zips.state)"   end

  def self.down     Post.find(:all).each { |post| post.update_attribute :state_id, nil }   end