I would need to rename a few columns in some tables in a SQLite database. I know that a similar question has been asked on stackoverflow previously, but it was for SQL in general, and the case of SQLite was not mentioned.
From the SQLite documentation for ALTER TABLE, I gather that it's not possible to do such a thing "easily" (i.e. a single ALTER TABLE statement).
I was wondering someone knew of a generic SQL way of doing such a thing with SQLite.
Modify column in table. You can not use the ALTER TABLE statement to modify a column in SQLite. Instead you will need to rename the table, create a new table, and copy the data into the new table.
To change a column name, enter the following statement in your MySQL shell: ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name; Replace table_name , old_column_name , and new_column_name with your table and column names.
Summary. Use the ALTER TABLE statement to modify the structure of an existing table. Use ALTER TABLE table_name RENAME TO new_name statement to rename a table. Use ALTER TABLE table_name ADD COLUMN column_definition statement to add a column to a table.
Say you have a table and need to rename "colb" to "col_b":
First you rename the old table:
ALTER TABLE orig_table_name RENAME TO tmp_table_name;
Then create the new table, based on the old table but with the updated column name:
CREATE TABLE orig_table_name ( col_a INT , col_b INT );
Then copy the contents across from the original table.
INSERT INTO orig_table_name(col_a, col_b) SELECT col_a, colb FROM tmp_table_name;
Lastly, drop the old table.
DROP TABLE tmp_table_name;
Wrapping all this in a BEGIN TRANSACTION;
and COMMIT;
is also probably a good idea.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With