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

score 7 · Accepted Answer · edited Aug 11 '22 at 14:09

7

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;

edited Aug 11 '22 at 14:09

guness

6,336
7
59
88

answered Jun 08 '20 at 19:05

Swayangjit

1,875
2
13
22

1

I saw the same question from 2018 I think. I would have thought that this would be fixed by now. Any tips on manually upgrading the table? I already had to do it once with 20 records, it wasn't a big problem but doing it again would be quite tedious and in the future there will be hundreds of records. – only-a-bitwise Jun 08 '20 at 19:26
1

Updated the answer. – Swayangjit Jun 08 '20 at 19:27
2

Note that the order of operations in this answer is one explicitly called out as incorrect in [the documentation](https://www.sqlite.org/lang_altertable.html). – Shawn Jun 08 '20 at 20:56
1

Thanks, @Shawn and user2864740. I have updated the answer please verify. – Swayangjit Jun 09 '20 at 06:50
Is there perhaps a generic way to do it? Some like what I see on removal of columns, as here: https://stackoverflow.com/a/51587449/878126 – android developer Dec 01 '20 at 12:27
I am getting this issue with SQLite version 3.32.2 2020-06-04 12:58:43 – Patriotic Feb 01 '21 at 12:57
The second step is incorrect. It should be `INSERT INTO item_tmp SELECT FROM item`. – Andy Jul 04 '21 at 22:37

score 1 · Answer 2 · answered Aug 20 '22 at 06:58

1

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.

answered Aug 20 '22 at 06:58

Chirag Thummar

665
6
16

SQLITE syntax error code 1 when renaming a column name

2 Answers2

Linked