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:

   CREATE TEMPORARY TABLE t1_backup(a,b);

   Copy after login

2. Insert data from original table to temporary table:

   INSERT INTO t1_backup SELECT a,b FROM t1;

   Copy after login

3. Delete original table:

   DROP TABLE t1;

   Copy after login

4. Create a new table without unwanted columns:

   CREATE TABLE t1(a,b);

   Copy after login

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

   INSERT INTO t1 SELECT a,b FROM t1_backup;

   Copy after login

6. Delete temporary table:

   DROP TABLE t1_backup;

   Copy after login

Remember to commit your changes to make them permanent:

COMMIT;

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!