How do I add a column in DatabaseHelper onUpgrade and set pre-existing rows to a particular value?
I tried:
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
...
if (oldVersion < 2) {
db.execSQL("ALTER TABLE " + MyTableName + " ADD COLUMN "
+ MyNewColumn + " TEXT;");
db.execSQL("UPDATE " + MyTableName + " SET " +
MyNewColumn + "=" + "value for existing;");
}
}
But I don't think the alter has been committed yet because it says the column doesn't exist. Any suggestion?
Edit: Added some more surrounding code
Edit: My mistake. I was just missing a quote around the value part and the error about no column threw me off, but the issue was it was looking for the column for the value I had used without the quote.
It's not as simple adding a column as you have shown as the onUpgrade will run every time you upgrade the database and adding the column a second time will fail... If you wish to do it quick and dirty you can wrap it in a try catch
try {
db.execSQL("ALTER TABLE sometable ADD COLUMN newcolumn integer");
}
catch(Exception e) {
e.printStackTrace();
}
A better way is to move all of the data out of the table into a temp table, drop and re-create the table and put your data back in. Here is an example from something I've used before
//Update old Table
if(DBVER< 1060){
List<String> columns = DB.GetColumns(db, "listitems");
db.execSQL("ALTER table listitems RENAME TO 'temp_listitems); ") ;
String tblListItems = "create table if not exists listitems " +
"(id integer primary key autoincrement, " +
"listid integer, " +
"itemid integer, " +
"qty integer, " +
"collected integer, " +
"qtytype integer, " +
"tabid integer); " ;
db.execSQL(tblListItems);
columns.retainAll(DB.GetColumns(db, "listitems"));
String cols = DB.join(columns, ",");
db.execSQL(String.format( "INSERT INTO %s (%s) SELECT %s from temp_%s", "listitems", cols, cols, "listitems"));
db.execSQL("DROP table 'temp_" + "listitems");
}
Heres a link http://www.devdaily.com/android/sqlite-alter-table-syntax-examples
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With