Rename Column if Exists sqlite?
sqlite
Solution
As far as I know there is no way to do what you ask. However you can use
SELECT sql FROM sqlite_master
WHERE tbl_name = 'table_name' AND type = 'table'
to check whether the column exists or not. However since you just have to rename the column once I do not know what the issue is with the recreation of the table. Recreation does NOT mean data loss.
The procedure would be along the lines of:
- `BEGIN TRANSACTION;`
- `ALTER TABLE table RENAME TO tmp_table;`
- `CREATE TABLE table (columnNames);`
- `INSERT INTO table(columnNames) SELECT columnNamesWrong FROM tmp_table;`
- `DROP TABLE tmp_table_name;`
- `COMMIT;`
If this is to much fuss use a tool to do it.
About the best practice part, it is best practice to get your tables named properly. Since you usually build all your queries around the field names renaming columns means breaking those queries. I do not know what you are looking for but the sqlite manual states:
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.
Note what is NOT possible.
Problem
I've created a column which was not being used initially but now we are setting and getting values from it. I found the column name is not correct, and I want to change it. Is there any query to check and rename a SQLite column, maybe something like this: ``` ALTER TABLE MyTable RENAME COLUMN IF EXISTS MyColumn TO MyColumn1; ``` Note: - I don't want to throw an exception - I don't want to recreate the table (I know both ways will accomplish the task but I'd rather live with poor name.)