Sqlite add column based on another columns value

Question

I have a first_name field in an SQLite database. I want to alter this database by adding a first_initial column and make its value the first character of first_name followed by a period. How do would I go about setting the value of first_initial, can it be done from the alter statement?

ALTER TABLE mytable ADD COLUMN first_initial TEXT;

Answer 1

As far as I know this cannot be done as part of the alter statement. However, you could follow up the alter statement with an update statement to initialize the initial:

UPDATE mytable SET first_initial = SUBSTR(first_name, 1, 1);

Or better yet, if the first_initial is always the first character of first_name, you don't need it in the table at all - you could just query it on demand, or, if you prefer, create a view to retrieve it:

CREATE VIEW myview AS
SELECT *, SUBSTR(first_name, 1, 1) AS first_initial
FROM   mytable