delete a column in sqlite android

Question

I want to delete a column in sqlite table. I am using DROP COLUMN but it is giving an error ADD or RENAME expected, got 'DROP'. How can I delete the column in this table?

    private static final String DATABASE_NAME = "Manager.db";
    public static final String TABLE_NAME = "MANAGER_TABLE";
    public static final String _ID = "_id";
    public static final String  STATUS = "status";

    public DBHelper(@Nullable Context context) {
        super(context, DATABASE_NAME, null, 2);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String create_table = "CREATE TABLE " + TABLE_NAME + "( "
                + "ID INTEGER PRIMARY KEY ,"
                + STATUS + " TEXT NOT NULL, "
                + _ID + " TEXT NOT NULL)";
        db.execSQL(create_table);

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion < 2) {
            db.execSQL("ALTER TABLE " + TABLE_NAME + " DROP COLUMN " + STATUS);
        }

SQLite supports DROP COLUMN since version 3.35.0 which is not supported yet by Android. — forpas, Nov 18 '21 at 14:29
Does this answer your question? [How to delete or add column in SQLITE?](https://stackoverflow.com/questions/8442147/how-to-delete-or-add-column-in-sqlite) — forpas, Nov 18 '21 at 14:30

MikeT · Answer 1 · 2021-11-19T08:49:29.737

You don't only need to remove the column in the onUpgrade method, you should also remove the column from the SQL in the onCreate method; otherwise a new installation will have the existing STATUS column.

This then simplifies removing the column as you can then call onCreate from the onUpgrade method to create the table exactly as it would be for a new installation.

Note if you have any other tables then you should change the create SQL to include IF NOT EXISTS (see below for an example).
Trying to create an existing table without IF NOT EXISTS will result in a failure.

so :-

Change the onCreate method to be

:-

@Override
public void onCreate(SQLiteDatabase db) {
    String create_table = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + "( "
            + "ID INTEGER PRIMARY KEY ,"
            // + STATUS + " TEXT NOT NULL, "// COMMENTED OUT
            + _ID + " TEXT NOT NULL)";
    db.execSQL(create_table);

}

adding IF NOT EXISTS isn't required but if used on any other tables (if any) then calling the onCreate method will not fail

Change the onUpgrade step to

:-

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (oldVersion < 2) {
        String suffix = "_old";
        db.execSQL("ALTER TABLE " + TABLE_NAME + " RENAME TO " + TABLE_NAME + suffix + ";");
        onCreate(db);
        /* Preserves existing data */
        db.execSQL("INSERT INTO " + TABLE_NAME + " SELECT ID," + _ID + " FROM " + TABLE_NAME + suffix + ";");
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME + suffix + ";");
    }
}

Working Example

The following is a working example that uses your DBHelper.

MainActivity adds a row according to the database version it being :-

public class MainActivity extends AppCompatActivity {

    DBHelper dbHelper;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        /* Testing */
        dbHelper = new DBHelper(this);
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        int version = db.getVersion();

        ContentValues cv = new ContentValues();
        cv.put(DBHelper._ID,"somedata");
        if (version == 1) {
            cv.put(DBHelper.STATUS,version);
        }
        db.insert(DBHelper.TABLE_NAME,null,cv);
    }
}

Run 1 For the first run Version 1 is used and the status column is kept as per :-

public DBHelper(@Nullable Context context) {
    super(context, DATABASE_NAME, null, 1);
}

@Override
public void onCreate(SQLiteDatabase db) {
    String create_table = "CREATE TABLE " + TABLE_NAME + "( "
            + "ID INTEGER PRIMARY KEY ,"
            + STATUS + " TEXT NOT NULL, "
            + _ID + " TEXT NOT NULL)";
    db.execSQL(create_table);
}

Using App Inspection (Database Inspector) shows :-

so the database has 1 row.

Run 2

For the second run the status column is commented out and the version changed to 2 and run resulting in:-

i.e. the existing data has been kept (bar the status). The table does not have the status column. Another row has been added.

Run 3

No code changes but the App was uninstalled to reflect a new installation at version 2, the result being just the 1 row added to the new version 2 table :-

Thank you for the answer, it worked. Just on the side note, what if there are multiple columns that are present and you only need to delete one column. Lets say we have `NAME`, `VALUE` , `_ID` and we want to keep those. How would you write this line `db.execSQL("INSERT INTO " + TABLE_NAME + " SELECT ID," + _ID + " FROM " + TABLE_NAME + suffix + ";"); `. — m umer, Nov 19 '21 at 09:40
@mumer The columns to extract follow the SELECT as a list separated by commas noting that the order should be the order in which they appear (order as per the CREATE SQL) unless the columns and order are specified within a pair of parenthesises. In the link see the COLUMNS in the flow diagram. Regarding a SELECT INSERT, read the section ***INSERT INTO table SELECT***. Link is https://sqlite.org/lang_insert.html — MikeT, Nov 19 '21 at 16:23

delete a column in sqlite android

1 Answers1