How to Effectively Drop Columns in SQLite Databases?

Delete column from SQLite table

You may encounter errors when trying to delete columns from a SQLite database using the ALTER TABLE table_name DROP COLUMN column_name query. To resolve this issue, consider the following options:

Check SQLite version

As of March 12, 2021 (3.35.0), SQLite supports the DROP COLUMN command. Please make sure you are using a compatible version of SQLite.

Recreate table

If using a newer SQLite version doesn't work, you can recreate the table to remove the columns you don't need. To do this:

1. Create a temporary table to store existing data:

   <code class="language-sql">CREATE TEMPORARY TABLE t1_backup(a,b);</code>

2. Insert data from original table to temporary table:

   <code class="language-sql">INSERT INTO t1_backup SELECT a,b FROM t1;</code>

3. Delete original table:

   <code class="language-sql">DROP TABLE t1;</code>

4. Create a new table without unwanted columns:

   <code class="language-sql">CREATE TABLE t1(a,b);</code>

5. Insert data from temporary table back into new table:

   <code class="language-sql">INSERT INTO t1 SELECT a,b FROM t1_backup;</code>

6. Delete temporary table:

   <code class="language-sql">DROP TABLE t1_backup;</code>

Remember to commit your changes to make them permanent:

<code class="language-sql">COMMIT;</code>

The above is the detailed content of How to Effectively Drop Columns in SQLite Databases?. For more information, please follow other related articles on the PHP Chinese website!