How to add two new Column to Android SQLite Database?

Question

I have one application on android market , now i want to change the database filed . I want to add two new field to particular table.Can you help me how to add two new columns to table ?

Here is my code.

 private static final String ALTER_USER_TABLE = "ALTER TABLE user_table ADD user_society text,user_street1 text;"; 

 @Override
 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 
 {
    db.execSQL(ALTER_USER_TABLE);
 }

I got the following error after executing .

W/System.err(  717): android.database.sqlite.SQLiteException: near ",": syntax error: ALTER TABLE user_table ADD user_society text,user_street1 text;
W/System.err(  717):    at android.database.sqlite.SQLiteDatabase.native_execSQL(Native Method)
W/System.err(  717):    at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1727)
W/System.err(  717):    at com.kbobs.org.database.Database$DatabaseHelper.onUpgrade(Database.java:48)
W/System.err(  717):    at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:108)
W/System.err(  717):    at com.kbobs.org.database.Database.OpenDatabase(Database.java:54)
W/System.err(  717):    at com.kbobs.org.ui.Login$2.handleMessage(Login.java:222)
W/System.err(  717):    at android.os.Handler.dispatchMessage(Handler.java:99)
W/System.err(  717):    at android.os.Looper.loop(Looper.java:123)
W/System.err(  717):    at android.app.ActivityThread.main(ActivityThread.java:4627)
W/System.err(  717):    at java.lang.reflect.Method.invokeNative(Native Method)
W/System.err(  717):    at java.lang.reflect.Method.invoke(Method.java:521)
W/System.err(  717):    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:868)
W/System.err(  717):    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:626)

Possible duplicate of [sqlite alter table add MULTIPLE columns in a single statement](http://stackoverflow.com/questions/6172815/sqlite-alter-table-add-multiple-columns-in-a-single-statement) — Sergey Glotov, Jul 11 '16 at 12:30

score 38 · Accepted Answer · answered May 11 '12 at 06:47

38

You can only add one column at a time. Split it into two ALTER TABLE statements and you should be fine.

private static final String ALTER_USER_TABLE_ADD_USER_SOCIETY = 
    "ALTER TABLE user_table ADD user_society TEXT";
private static final String ALTER_USER_TABLE_ADD_USER_STREET1 = 
    "ALTER TABLE user_table ADD user_street1 TEXT";

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 
{
   db.execSQL(ALTER_USER_TABLE_ADD_USER_SOCIETY);
   db.execSQL(ALTER_USER_TABLE_ADD_USER_STREET1);
}

answered May 11 '12 at 06:47

Jens

16,853
4
55
52

1

Thank you very much for your precious time for helping me a lot. – Chirag May 11 '12 at 07:00
1

It is possible if you create a temporary table and and copy the rows of the existing table to temporary table. Drop the existing table and create the one again and copy from the temporary table. String table_temp = "create TEMPORARY table "+ tempTableName + ....."; – Tarun May 11 '12 at 07:55
1

@DEV since it's old question but for reference to others coming here: You can't do these operations in a single query(alter), you must do one per operation. – zed Oct 24 '17 at 13:34

score 0 · Answer 2 · answered Oct 04 '19 at 12:44

The right way to add new column to DB, for example in version 2, would be:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (oldVersion < 2) {
         db.execSQL("ALTER TABLE mytable ADD COLUMN mycolumn TEXT");
    }
}

It covers all pitfalls, including major issue: if a user goes from version 1 to 3 they will miss the upgrade query completely! These users will be in an awkward limbo where they are missing a few of the intermediate updates and do not have the expected sql schema.

Also don't forget to alter the create statement adding new column.

So, I could add a new column by using this solution, but it removes the existing data with database and the new one is empty. Why did it happen? — Mark Delphi, Jan 27 '21 at 21:15

How to add two new Column to Android SQLite Database?

2 Answers2