Delete column from SQLite table

Question

I have a problem  I need to delete a column from my SQLite database  I wrote this query  alter table table name drop column column name    but it does not work  Please help me

User · Answer

Just in case if it could help someone like me.

Based on the Official website and the Accepted answer, I made a code using C# that uses System.Data.SQLite NuGet package.

This code also preserves the Primary key and Foreign key.

CODE in C#:

void RemoveColumnFromSqlite (string tableName, string columnToRemove) {
 try {
    var mSqliteDbConnection = new SQLiteConnection ("Data Source=db_folder\\MySqliteBasedApp.db;Version=3;Page Size=1024;");
    mSqliteDbConnection.Open ();             
    // Reads all columns definitions from table
    List<string> columnDefinition = new List<string> ();
    var mSql = $"SELECT type, sql FROM sqlite_master WHERE tbl_name='{tableName}'";
    var mSqliteCommand = new SQLiteCommand (mSql, mSqliteDbConnection);
    string sqlScript = "";
    using (mSqliteReader = mSqliteCommand.ExecuteReader ()) {
       while (mSqliteReader.Read ()) {
          sqlScript = mSqliteReader["sql"].ToString ();
          break;
       }
    }
    if (!string.IsNullOrEmpty (sqlScript)) {
       // Gets string within first '(' and last ')' characters
       int firstIndex = sqlScript.IndexOf ("(");
       int lastIndex = sqlScript.LastIndexOf (")");
       if (firstIndex >= 0 && lastIndex <= sqlScript.Length - 1) {
          sqlScript = sqlScript.Substring (firstIndex, lastIndex - firstIndex + 1);
       }
       string[] scriptParts = sqlScript.Split (new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
       foreach (string s in scriptParts) {
          if (!s.Contains (columnToRemove)) {
             columnDefinition.Add (s);
          }
       }
    }
    string columnDefinitionString = string.Join (",", columnDefinition);
    // Reads all columns from table
    List<string> columns = new List<string> ();
    mSql = $"PRAGMA table_info({tableName})";
    mSqliteCommand = new SQLiteCommand (mSql, mSqliteDbConnection);
    using (mSqliteReader = mSqliteCommand.ExecuteReader ()) {
       while (mSqliteReader.Read ()) columns.Add (mSqliteReader["name"].ToString ());
    }
    columns.Remove (columnToRemove);
    string columnString = string.Join (",", columns);
    mSql = "PRAGMA foreign_keys=OFF";
    mSqliteCommand = new SQLiteCommand (mSql, mSqliteDbConnection);
    int n = mSqliteCommand.ExecuteNonQuery ();
    // Removes a column from the table
    using (SQLiteTransaction tr = mSqliteDbConnection.BeginTransaction ()) {
       using (SQLiteCommand cmd = mSqliteDbConnection.CreateCommand ()) {
          cmd.Transaction = tr;
          string query = $"CREATE TEMPORARY TABLE {tableName}_backup {columnDefinitionString}";
          cmd.CommandText = query;
          cmd.ExecuteNonQuery ();
          cmd.CommandText = $"INSERT INTO {tableName}_backup SELECT {columnString} FROM {tableName}";
          cmd.ExecuteNonQuery ();
          cmd.CommandText = $"DROP TABLE {tableName}";
          cmd.ExecuteNonQuery ();
          cmd.CommandText = $"CREATE TABLE {tableName} {columnDefinitionString}";
          cmd.ExecuteNonQuery ();
          cmd.CommandText = $"INSERT INTO {tableName} SELECT {columnString} FROM {tableName}_backup;";
          cmd.ExecuteNonQuery ();
          cmd.CommandText = $"DROP TABLE {tableName}_backup";
          cmd.ExecuteNonQuery ();
       }
       tr.Commit ();
    }
    mSql = "PRAGMA foreign_keys=ON";
    mSqliteCommand = new SQLiteCommand (mSql, mSqliteDbConnection);
    n = mSqliteCommand.ExecuteNonQuery ();
 } catch (Exception ex) {
    HandleExceptions (ex);
 }
}

User · Answer

In Python 3 8    Preserves primary key and column types  Takes 3 inputs   a sqlite cursor  db cur  table name  t and  list of columns to junk  columns to junk  def removeColumns db cur  t  columns to junk          Obtain column information     sql    quot PRAGMA table info  quot    t    quot   quot      record   query db cur  sql         Initialize two strings  one for column names   column types and one just       for column names     cols w types    quot   quot      cols    quot  quot         Build the strings  filtering for the column to throw out     for r in record          if r 1  not in columns to junk              if r 5     0                  cols w types    r 1     quot   quot    r 2     quot   quot              if r 5     1                  cols w types    r 1     quot   quot    r 2     quot  PRIMARY KEY  quot              cols    r 1     quot   quot         Cut potentially trailing commas     if cols w types -1      quot   quot           cols w types   cols w types  -1      else          pass      if cols -1      quot   quot           cols   cols  -1      else          pass        Execute SQL     sql    quot CREATE TEMPORARY TABLE xfer  quot    cols w types    quot   quot      db cur execute sql      sql    quot INSERT INTO xfer SELECT  quot    cols    quot  FROM  quot    t     db cur execute sql      sql    quot DROP TABLE  quot    t     db cur execute sql      sql    quot CREATE TABLE  quot    t   cols w types    quot   quot      db cur execute sql      sql    quot INSERT INTO  quot    t    quot  SELECT  quot    cols     quot  FROM xfer quot      db cur execute sql   You ll find a reference to a query   function   Just a helper    Takes two inputs   sqlite cursor db cur and  the query string  query  def query db cur  query        r   db cur execute query  fetchall        return r  Don t forget to include a  quot commit   quot

User · Answer

PRAGMA foreign keys off   BEGIN TRANSACTION   ALTER TABLE table1 RENAME TO  table1 old   CREATE TABLE table1     column1 datatype   NULL   NOT NULL      column2 datatype   NULL   NOT NULL              INSERT INTO table1  column1  column2      column n    SELECT column1  column2      column n   FROM  table1 old   COMMIT   PRAGMA foreign keys on    For more info  https   www techonthenet com sqlite tables alter table php

User · Answer

This option works only if you can open the DB in a DB Browser like DB Browser for SQLite   In DB Browser for SQLite    Go to the tab   Database Structure   Select you table Select Modify table  just under the tabs  Select the column you want to delete Click on Remove field and click OK

User · Answer

For simplicity  why not create the backup table from the select statement   CREATE TABLE t1 backup AS SELECT a  b FROM t1  DROP TABLE t1  ALTER TABLE t1 backup RENAME TO t1

User · Answer

In case anyone needs a  nearly  ready-to-use PHP function  the following is based on this answer          Remove a column from a table          param string  tableName The table to remove the column from      param string  columnName The column to remove from the table      public function DropTableColumn  tableName   columnName           --        Determine all columns except the one to remove        columnNames   array          statement    pdo- gt prepare  PRAGMA table info  tableName           statement- gt execute array          rows    statement- gt fetchAll PDO  FETCH OBJ         hasColumn   false       foreach   rows as  row                if strtolower  row- gt name      strtolower  columnName                         array push  columnNames   row- gt name                     else                        hasColumn   true                          Column does not exist in table  no need to do anything      if     hasColumn   return          --        Actually execute the SQL        columns   implode         columnNames         statement    pdo- gt exec          CREATE TABLE  t1 backup  AS SELECT   columns  FROM   tableName           DROP TABLE   tableName           ALTER TABLE  t1 backup  RENAME TO   tableName          In contrast to other answers  the SQL used in this approach seems to preserve the data types of the columns  whereas something like the accepted answer seems to result in all columns to be of type TEXT   Update 1   The SQL used has the drawback that autoincrement columns are not preserved

User · Answer

I ve made a Python function where you enter the table and column to remove as arguments   def removeColumn table  column       columns          for row in c execute  PRAGMA table info     table                 columns append row 1       columns remove column      columns   str columns      columns   columns replace               columns   columns replace               for i in                           columns   columns replace i          c execute  CREATE TABLE temptable AS SELECT     columns     FROM     table      c execute  DROP TABLE     table      c execute  ALTER TABLE temptable RENAME TO     table      conn commit     As per the info on Duda s and MeBigFatGuy s answers this won t work if there is a foreign key on the table  but this can be fixed with 2 lines of code  creating a new table and not just renaming the temporary table

User · Answer

Instead of dropping the backup table  just rename it     BEGIN TRANSACTION  CREATE TABLE t1 backup a b   INSERT INTO t1 backup SELECT a b FROM t1  DROP TABLE t1  ALTER TABLE t1 backup RENAME TO t1  COMMIT

User · Answer

From  http   www sqlite org faq html       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  If you   want to make more complex changes in the structure of a table  you   will have to recreate the table  You can save existing data to a   temporary table  drop the old table  create the new table  then copy   the data back in from the temporary 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

User · Answer

For SQLite3 c      void GetTableColNames  tstring sTableName   std  vector lt tstring gt   pvsCols         UASSERT pvsCols        CppSQLite3Table table1       tstring sDML   StringOps  std sprintf  T  SELECT   FROM  s     sTableName c str             table1   getTable  StringOps  tstringToUTF8string sDML  c str           for   int nCol   0   nCol  lt  table1 numFields     nCol                   const char  pch1   table1 fieldName nCol              pvsCols- gt push back  StringOps  UTF8charTo tstring pch1              bool ColExists  tstring sColName         bool bColExists   true       try               tstring sQuery   StringOps  std sprintf  T  SELECT  s FROM MyOriginalTable LIMIT 1      sColName c str               ShowVerbalMessages false            CppSQLite3Query q   execQuery  StringOps  tstringTo stdString sQuery  c str               ShowVerbalMessages true             catch  CppSQLite3Exception amp  e                bColExists   false             return bColExists     void DeleteColumns  std  vector lt tstring gt   pvsColsToDelete         UASSERT pvsColsToDelete        execDML  StringOps  tstringTo stdString  T  begin transaction     c str            std  vector lt tstring gt  vsCols      GetTableColNames   T  MyOriginalTable      amp vsCols          CreateFields   T  TempTable1     false         tstring sFieldNamesSeperatedByCommas       for   int nCol   0   nCol  lt  vsCols size     nCol                    tstring sColNameCurr   vsCols at nCol            bool bUseCol   true           for   int nColsToDelete   0  nColsToDelete  lt  pvsColsToDelete- gt size     nColsToDelete                           if   pvsColsToDelete- gt at nColsToDelete     sColNameCurr                                 bUseCol   false                  break                                   if   bUseCol               sFieldNamesSeperatedByCommas    sColNameCurr    T                    if   sFieldNamesSeperatedByCommas at  int sFieldNamesSeperatedByCommas size    - 1      T               sFieldNamesSeperatedByCommas erase  int sFieldNamesSeperatedByCommas size    - 1         tstring sDML        sDML   StringOps  std sprintf  T  insert into TempTable1 SELECT  s FROM MyOriginalTable  n     sFieldNamesSeperatedByCommas c str          execDML  StringOps  tstringTo stdString sDML  c str           sDML   StringOps  std sprintf  T  ALTER TABLE MyOriginalTable RENAME TO MyOriginalTable old n          execDML  StringOps  tstringTo stdString sDML  c str           sDML   StringOps  std sprintf  T  ALTER TABLE TempTable1 RENAME TO MyOriginalTable n          execDML  StringOps  tstringTo stdString sDML  c str            sDML      T  DROP TABLE MyOriginalTable old              execDML  StringOps  tstringTo stdString sDML  c str            execDML  StringOps  tstringTo stdString  T  commit transaction     c str

User · Answer

Create a new table directly with the following query   CREATE TABLE table name  Column 1 TEXT Column 2 TEXT       Now insert the data into table name from existing table with the following query   INSERT INTO table name  Column 1 Column 2  FROM existing table      Now drop the existing table by following query   DROP TABLE existing table

[sql] Delete column from SQLite table

Examples related to sql

Examples related to sqlite

Examples related to ddl