Delete Column from Sqlite Table

How to delete or add column in SQLITE?

ALTER TABLE SQLite

SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table. It is not possible to rename a column, remove a column, or add or remove constraints from a table.

You can:

1. create new table as the one you are trying to change,
2. copy all data,
3. drop old table,
4. rename the new one.

Delete column from SQLite table

Update: SQLite 2021-03-12 (3.35.0) now supports DROP COLUMN. The FAQ on the website is still outdated.

---

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;

DROP COLUMN IF EXISTS in SQLite?

This recent SQLite forum question on the same subject is answered by the primary SQLite developer, Richard Hipp. Based on the Richard's answer, and the observation from @AnonCoward, the following is true:

As of v3.37.2 (January 2022), SQLite does NOT support either of the following:

ALTER TABLE ... ADD COLUMN IF NOT EXISTS ...;
ALTER TABLE ... DROP COLUMN IF EXISTS ...;

UPDATE

There is currently a prototype implementation of this functionality in SQLite. The git check-in can be viewed here. Not slated for release in the upcoming v3.38.0 release, but sometime in the future.

How do I delete column from sqlite table in android?

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.

How to delete the multiple columns from the SQL table in SQlite3?

Is this what you want?

update mytable set person_1 = null, person_5 = null
where phone_no = 1234

I understand that by delete columns person_1 and person_3 where phone_no is 1234 you mean set values to null in columns person_1 and person_3 where phone_no is 1234.

If you want to actually remove the columns, then it's a different question. In SQLite, you need to recreate the table:

create table tmp_table(
    name varchar(50),         -- adapt the datatypes and lengths to your requirement
    phone_no int,
    person_2 varchar(50),
    person_4 varchar(50)
);
insert into tmp select name, phone, person_2, person_4 from mytable;
drop table mytable;

create table mytable(
    name varchar(50),
    phone_no int,
    person_2 varchar(50),
    person_4 varchar(50)
);
insert into mytable select name, phone, person_2, person_4 from tmp_table;
drop table tmp_table;   

How can I remove a column from my SQLite table with sqlite-net-pcl that comes with Xamarin Forms?

SQLite does not support ALTER TABLE x DROP COLUMN x so you need to create a new table and copy data.

You can do all this via a single multi-line SQL statement and execute it, but this will walk you though the steps using the ORM as much as possible:

Note: This assumes that your model has be updated and does not include that column anymore and your current database might or might not have it...

var conn = new SQLiteConnection(.....
conn.CreateTable();
~~~

if (0 < conn.ExecuteScalar("SELECT COUNT(*) AS colcount FROM pragma_table_info('ViewHistory') WHERE name='Assign'"))
{
    try
    {
        conn.BeginTransaction();
        conn.Execute("ALTER TABLE ViewHistory RENAME TO backup;");
        conn.CreateTable();

        // Column map and copy data 
        var columns = conn.GetMapping(CreateFlags.None).Columns;
        var columnNames = string.Join(",", columns.Select(p => p.Name));
        conn.Execute($"INSERT INTO ViewHistory ({columnNames}) SELECT {columnNames} FROM backup;");

        conn.Execute("DROP TABLE backup;");
        conn.Commit();
        conn.Execute("VACUUM");
    }
    catch (Exception ex)
    {
        conn.Rollback();
        throw ex;
    }
}

Note: Typically I just use "DB Browser for SQLite", make all the table/column alterations to the database and copy the "DB Log output" that contains all the SQL statements and paste that into a single SQLConnnection.Exceute statement...

How django drops column in SQLite

To drop a column in an SQLite database Django follows the procedure described here: https://www.sqlite.org/lang_altertable.html#caution

Which in simple words is create a new table, copy data from old table, delete old table and then rename new table.

from the source code [GitHub] We can see that the schema editor for SQLite calls self._remake_table(model, delete_field=field) in the method remove_field which is what is used to drop a column. The method _remake_table has the following docstring in it's code which describes how exactly the process is performed:

Shortcut to transform a model from old_model into new_model This
follows the correct procedure to perform non-rename or column addition
operations based on SQLite's documentation
https://www.sqlite.org/lang_altertable.html#caution The essential
steps are:

1. Create a table with the updated definition called "new__app_model"
2. Copy the data from the existing "app_model" table to the new table
3. Drop the "app_model" table
4. Rename the "new__app_model" table to "app_model"
5. Restore any index of the previous "app_model" table.

---

Related Topics