# Can i do an sql update in a rails migration?

**URL:** <https://www.ruby-forum.com/t/can-i-do-an-sql-update-in-a-rails-migration/122227>\
**Category:** Rails\
**Created:** [December 5, 2007, 6:54pm UTC](https://www.ruby-forum.com/t/can-i-do-an-sql-update-in-a-rails-migration/122227 "2007-12-05T18:54:39Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![cmaxvv](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/85f322/32.png) [@cmaxvv](https://www.ruby-forum.com/u/cmaxvv)\
**Post date:** [December 5, 2007, 6:54pm UTC](https://www.ruby-forum.com/t/can-i-do-an-sql-update-in-a-rails-migration/122227/1 "2007-12-05T18:54:39Z")

</div>

In order to do a single table inheritance, i added a new field called  
‘type’, and set the value of it to either “Course” or “Lesson”, based on  
another field called “is\_course”, which is boolean.

I set up a callback to set the value of type whenever a record is saved,  
and i set all the previously existing records values for ‘type’ with an  
SQL update, via the rails helper update\_all, eg

Item.update\_all(‘type = “Lesson”’) #is\_course defaults to 0, ie Lesson  
is default  
Item.update\_all(‘type = “Course”’, ‘is\_course = 1’)

That all worked fine, no problems. But it occurred to me that if the db  
migration version is rolled back and forward again, ie the ‘type’ column  
is removed and then re-added, then the values will be lost. Is there  
any way to write the update into the same migration that adds the  
column, so that it will be run again if the column has to be re-created?

---

<div class="post-metadata">

**Author:** ![cmaxvv](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/85f322/32.png) [@cmaxvv](https://www.ruby-forum.com/u/cmaxvv)\
**Post date:** [December 5, 2007, 7:04pm UTC](https://www.ruby-forum.com/t/can-i-do-an-sql-update-in-a-rails-migration/122227/2 "2007-12-05T19:04:41Z")

</div>

Max W. wrote:

> In order to do a single table inheritance, i added a new field called  
> ‘type’, and set the value of it to either “Course” or “Lesson”, based on  
> another field called “is\_course”, which is boolean.
> 
> I set up a callback to set the value of type whenever a record is saved,  
> and i set all the previously existing records values for ‘type’ with an  
> SQL update, via the rails helper update\_all, eg
> 
> Item.update\_all(‘type = “Lesson”’) #is\_course defaults to 0, ie Lesson  
> is default  
> Item.update\_all(‘type = “Course”’, ‘is\_course = 1’)
> 
> That all worked fine, no problems. But it occurred to me that if the db  
> migration version is rolled back and forward again, ie the ‘type’ column  
> is removed and then re-added, then the values will be lost. Is there  
> any way to write the update into the same migration that adds the  
> column, so that it will be run again if the column has to be re-created?

Never mind, I’m an idiot. I just wrote the update\_all call straight  
into the migration and it works. Don’t know why i didn’t think to try  
that before asking…

---

<div class="post-metadata">

**Author:** ![cmaxvv](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/85f322/32.png) [@cmaxvv](https://www.ruby-forum.com/u/cmaxvv)\
**Post date:** [December 5, 2007, 7:28pm UTC](https://www.ruby-forum.com/t/can-i-do-an-sql-update-in-a-rails-migration/122227/3 "2007-12-05T19:28:37Z")

</div>

To answer your original question, you can use exec to run arbitrary  
sql statements. SQLite does not allow the modification of a column  
once it’s added to a table, so if you must change the definition of a  
column (like change it from a varchar to a text), you have to do all  
of the raw sql of creating a temp table, copying the data, dropping  
the table, recreating the table, copying the data back, and then  
dropping the temp table. I do all of that with exec.

Peace,  
Phillip