SQLITE syntax error code 1 when renaming a column name

Question

I am migrating a Room database in my Android app. This is the migration code:

static final Migration MIGRATION_1_2 = new Migration(1, 2) {
    @Override
    public void migrate(SupportSQLiteDatabase database) {
        database.execSQL("ALTER TABLE item RENAME itemInfoId TO itemId");
    }
};

The error message

android.database.sqlite.SQLiteException: near "itemInfoId": syntax error (code 1 SQLITE_ERROR): , while compiling: ALTER TABLE item RENAME itemInfoId TO itemId

I have also tried the SQL of "ALTER TABLE item RENAME COLUMN itemInfoId TO itemId", same error

android.database.sqlite.SQLiteException: near "COLUMN": syntax error (code 1 SQLITE_ERROR): , while compiling: ALTER TABLE item RENAME COLUMN itemInfoId TO itemId

Answer 1

Rename keyword is available in SQLite version 3.25 which is not available for the latest android version. You will have to manually upgrade the table

1. Create item_tmp table with correct column value itemId

CREATE TABLE item_tmp(<column1> <data_type>, itemId <data_type>,.....)

2. Copy the data from item to item_tmp

INSERT INTO item_tmp(<column1>, <column2>,..) 
    SELECT <column1>, <column1>, ... 
    FROM item;

3. Drop table item

DROP TABLE item;

4. Rename the item_tmp table

ALTER TABLE item_tmp RENAME TO item;

Answer 2

i have faced same problem while using RENAME keyword in Sqlite. it gives error in below android 10 device and working perfectly in android 11 and above.