Migration update with multiple joined tables

Rails
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