# Migration update with multiple joined tables

**URL:** <https://www.ruby-forum.com/t/migration-update-with-multiple-joined-tables/186671>\
**Category:** Rails\
**Created:** [April 11, 2010, 1:24am UTC](https://www.ruby-forum.com/t/migration-update-with-multiple-joined-tables/186671 "2010-04-11T01:24:56Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![davidjrice](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/5e9695/32.png) [@davidjrice](https://www.ruby-forum.com/u/davidjrice)\
**Post date:** [April 11, 2010, 1:24am UTC](https://www.ruby-forum.com/t/migration-update-with-multiple-joined-tables/186671/1 "2010-04-11T01:24:56Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![davidjrice](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/5e9695/32.png) [@davidjrice](https://www.ruby-forum.com/u/davidjrice)\
**Post date:** [April 11, 2010, 1:55am UTC](https://www.ruby-forum.com/t/migration-update-with-multiple-joined-tables/186671/2 "2010-04-11T01:55:28Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![davidjrice](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/5e9695/32.png) [@davidjrice](https://www.ruby-forum.com/u/davidjrice)\
**Post date:** [April 11, 2010, 2:08am UTC](https://www.ruby-forum.com/t/migration-update-with-multiple-joined-tables/186671/3 "2010-04-11T02:08:16Z")

</div>

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