How do I update a table in sqlite?
I have two tables: "TABLE_SUBJECT" and the second one is "TABLE_CHAPTER". Now I want to use add column and delete the previous one. My question is how to do this. I'm trying to update but it displays the previous ones not new ones. I need to delete the previous one and update the table with the new column.
I change the version number but doesn't work. Please give me a hint or link.
Here is my SQLite code:
@Override
public void onCreate(SQLiteDatabase database) {
database.execSQL(DATABASE_CREATE);
database.execSQL(DATABASE_CREATE1);
}
@Override
public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
Log.w(MySQLiteHelper.class.getName(), "Upgrading database from version "
+ oldVersion + " to " + newVersion
+ ", which will destroy all old data");
database.execSQL("DROP TABLE IF EXISTS " + TABLE_SUBJECT);
database.execSQL("DROP TABLE IF EXISTS" + TABLE_CHAPTER);
onCreate(database);
}
source to share
try this code
public long update_todo_not(String a, String b, String c, String d,
String e, String f, String g, String id) {
ContentValues con = new ContentValues();
con.put("title", a);
con.put("description", b);
con.put("due_date", c);
con.put("alarm_time", d);
con.put("category", e);
con.put("alarm_set",f);
con.put("priority", g);
Log.v("priority", g+"");
return mDb.update(DATABASE_TABLE_TODO_LIST, con, "id ='" + id + "'",null);
}
source to share
You cannot drop columns in SQLite. There is a workaround described here in the FAQ .
To add a new column, you can simply increase the database version and change the create strings you got to the database format you want (remove the column you want to remove and add another one). When the database is open, onUpgrade will be called, which will drop the existing tables. After that, the modified tables are created. This is not a good method, because you are losing all data in the database.
To add a new column without losing all data, you need to change the onUpgrade method to something like this (this assumes your current database version is 1, if not just change the number in case) and then increase the database version data
@Override
public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
switch (oldVersion) {
case 1:
database.execSQL("ALTER TABLE " +
TABLE_SUBJECT + " ADD COLUMN " + KEY_NEWCOLUMN + " text not null");
database.execSQL("ALTER TABLE " +
TABLE_CHAPTER + " ADD COLUMN " + KEY_NEWCOLUMN + " text not null");
}
}
This method is scalable because when new changes come in, you can just add case 2, case 3 ... Don't add a break at the end of the case. So if an app update increases the version to 3 and the updated app is still included 1 (it may have missed the update) all changes are applied.
Here you can find the documentation for the alter table. The sqlite site (sqlite.org) is usually very helpful in finding such things.
source to share