Rails - Apr 2010 - Migration update with multiple joined tables

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
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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.
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.