How to delete or add column in SQLITE?

Question

I want to delete or add column in sqlite database

I am using following query to delete column.

ALTER TABLE TABLENAME DROP COLUMN COLUMNNAME

But it gives error

System.Data.SQLite.SQLiteException: SQLite error
near "DROP": syntax error

Answer 1

http://www.sqlite.org/lang_altertable.html

As you can see in the diagram, only ADD COLUMN is supported. There is a (kinda heavy) workaround, though: http://www.sqlite.org/faq.html#q11

Answer 2

You can also now use DB browser for SQLite to manipulate columns

Answer 3

public void DeleteColFromTable(String DbName, String TableName, String ColName){
    SQLiteDatabase db = openOrCreateDatabase(""+DbName+"", Context.MODE_PRIVATE, null);
    db.execSQL("CREATE TABLE IF NOT EXISTS "+TableName+"(1x00dff);");
    Cursor c = db.rawQuery("PRAGMA table_info("+TableName+")", null);
    if (c.getCount() == 0) {

    } else {
        String columns1 = "";
        String columns2 = "";
        while (c.moveToNext()) {
            if (c.getString(1).equals(ColName)) {
            } else {
                columns1 = columns1 + ", " + c.getString(1) + " " + c.getString(2);
                columns2 = columns2 + ", " + c.getString(1);
            }
            if (c.isLast()) {
                db.execSQL("CREATE TABLE IF NOT EXISTS DataBackup (" + columns1 + ");");
                db.execSQL("INSERT INTO DataBackup SELECT " + columns2 + " FROM "+TableName+";");
                db.execSQL("DROP TABLE "+TableName+"");
                db.execSQL("ALTER TABLE DataBackup RENAME TO "+TableName+";");
            }
        }
    }
}

and just call a method

DeleteColFromTable("Database name","Table name","Col name which want to delete");

Answer 4

As SQLite has limited support to ALTER TABLE so you can only ADD column at end of the table OR CHANGE TABLE_NAME in SQLite.

Here is the Best Answer of HOW TO DELETE COLUMN FROM SQLITE?

visit Delete column from SQLite table

Answer 5

you can use Sqlitebrowser. In the browser mode, for the respective database and the table, under the tab -database structure,following the option Modify Table, respective column could be removed.

Answer 6

Implementation in Python based on information at http://www.sqlite.org/faq.html#q11.

import sqlite3 as db
import random
import string

QUERY_TEMPLATE_GET_COLUMNS = "PRAGMA table_info(@table_name)"
QUERY_TEMPLATE_DROP_COLUMN = """
  BEGIN TRANSACTION;
  CREATE TEMPORARY TABLE @tmp_table(@columns_to_keep);
  INSERT INTO @tmp_table SELECT @columns_to_keep FROM @table_name;
  DROP TABLE @table_name;
  CREATE TABLE @table_name(@columns_to_keep);
  INSERT INTO @table_name SELECT @columns_to_keep FROM @tmp_table;
  DROP TABLE @tmp_table;
  COMMIT;
"""

def drop_column(db_file, table_name, column_name):
    con = db.connect(db_file)
    QUERY_GET_COLUMNS = QUERY_TEMPLATE_GET_COLUMNS.replace("@table_name", table_name)
    query_res = con.execute(QUERY_GET_COLUMNS).fetchall()
    columns_list_to_keep = [i[1] for i in query_res if i[1] != column_name]
    columns_to_keep = ",".join(columns_list_to_keep)
    tmp_table = "tmp_%s" % "".join(random.sample(string.ascii_lowercase, 10))
    QUERY_DROP_COLUMN = QUERY_TEMPLATE_DROP_COLUMN.replace("@table_name", table_name)\
        .replace("@tmp_table", tmp_table).replace("@columns_to_keep", columns_to_keep)
    con.executescript(QUERY_DROP_COLUMN)
    con.close()

drop_column(DB_FILE, TABLE_NAME, COLUMN_NAME)

This script first makes random temporary table and inserts data of only necessary columns except the one that will will be dropped. Then restores the original table based on the temporary table and drops the temporary table.