I have ‘Name, Age, Gender’ columns in a ‘test’ table.
I now, want to create another table ‘test1’ which will have these columns :
‘Name, Age, Gender, Occupation’.
How do I do it without creating and then altering? Is there any way to do it in a single line?
I tried adding one column, but I was able to add it at the start only, like:
CREATE TABLE test1 (Occupation VARCHAR(50)) LIKE test;
This would result in ‘Occupation, Name, Age, Gender’.
I tried CREATE TABLE test1 LIKE test (Occupation VARCHAR(50)) which gave me an error.
How to add columns at last while creation?
>Solution :
You may use CREATE TABLE AS ... with a select containing the new column:
CREATE TABLE test1 AS
SELECT Name, Age, Gender, '' AS Occupation
FROM test;
The above simply places empty string as the value for the new occupation column. If you want some other value, then you would need to provide logic for how to populate it.