How to update table in sqlite?

Question

I have two table first is "TABLE_SUBJECT" and second is "TABLE_CHAPTER".Now i want to
add some column and delete previous. My question is that how to do that. i try to update but
it display previous not new. I need to delete previous and update table with new column.
I change version number but it not working. Please give me hint or reference.

Here is my some code of SQLite:

@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);
}

score 6 · Accepted Answer · edited Jun 10 '13 at 19:38

you 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);

}

score 3 · Answer 2 · answered Mar 22 '12 at 11:35

You can't delete columns in SQLite. There is a workaround descriped here in the FAQ.

To add a a new column you could simply increase the database version and change the create strings you got to the desired database layout (remove the column you want to delete and add the other). When the database is next opened onUpgrade is called, which will delete the existing tables. After that the changed tables are created. This isn't a good method, because you loose all data in the database.

To add a new column without loosing all data use you have to change the onUpgrade method to something like this (this anticipates that you current database version is 1, if not simply change the number at case) and than increase the database version:

@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 there are new changes you can simply add case 2, case 3... Do not add break at the end of case. This way if an app update increases the version to 3 and the updated app is still on 1 (maybe it missed an update) all changes are applied.

Here you can find the documentation for alter table. The sqlite site (sqlite.org) is in general a great help to find this kind of things.

I've just read your answer and I was just wondering how does app know what version of the database it's on? The original question only has `database.execSQL(DATABASE_CREATE);` which doesn't state what version the database is, I'm facing the same problem at the moment — Sjharrison, Oct 07 '15 at 13:46

How to update table in sqlite?

2 Answers2

Linked