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?

Answer 1

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.

Answer 2

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