How do I delete column from sqlite table in android?

Question

I tried deleting a column by using the following

openDB.execSQL("ALTER TABLE favs" + " DROP COLUMN favsCount");

LogCat gives the following message:

11-07 21:18:29.238: ERROR/Database(13952): Failure 1 (near "DROP": syntax error) on 0x34e550 when preparing 'ALTER TABLE favs DROP COLUMN favsCount'.

Is it not possible to delete fields in sqlite for Android?

mu is too short · Accepted Answer · 2011-11-08 02:35:20Z

49

Sorry, SQLite doesn't support DROP COLUMN:

(11) How do I add or delete columns from an existing table in SQLite.

SQLite has limited ALTER TABLE support that you can use to add a column to the end of a table or to change the name of a table. [...]

For example, suppose you have a table named "t1" with columns names "a", "b", and "c" and that you want to delete column "c" from this table. The following steps illustrate how this could be done:
BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a,b);
INSERT INTO t1 SELECT a,b FROM t1_backup;
DROP TABLE t1_backup;
COMMIT;

So basically, you have to use the "copy, drop table, create new table, copy back" technique to remove a column.

answered Nov 8, 2011 at 2:35

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

EmmanuelMess Over a year ago

Beware of the "BEGIN TRANSACTION;" and "COMMIT;" because they can block the database, as a transaction has already been commenced in SQLiteOpenHelper.getWritableDatabase().

mu is too short Over a year ago

@Suragch Check the documentation for SQLiteOpenHelper.getWritableDatabase() (assuming that's what you're using of course). That bit of example SQL is quoted from the SQLite documentation BTW.

Suragch Over a year ago

OK. I didn't know if you had any personal knowledge or experience with it. Sometimes new users leave random warnings when they don't know what they are talking about.

FutureShocked Over a year ago

If you're writing a Room migration with, note that database.execSQL works only with single statements at a time, i.e. this would become database.execSQL(CREATE TEMPORARY TABLE...) database.execSQL(INSERT INTO ...) etc

Usman Rana Over a year ago

where from will it get type of each column when creating t1_backup?

|

Jordi Coscolla · Accepted Answer · 2011-11-08 05:53:44Z

5

as mu is too short says Sqlite doesn't allow to do an alter table to delete a column. here you can see the alter syntax definition

answered Nov 8, 2011 at 5:53

Jordi Coscolla

1,0666 silver badges8 bronze badges

Collectives™ on Stack Overflow

How do I delete column from sqlite table in android?

2 Answers 2

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related