how to add new date column while creating table from existing table in mysql-CodePudding

** I want to add date_col to new_table_name, I am trying below query but getting error **

        CREATE TABLE IF NOT EXISTS new_table_name AS
           SELECT A.employee AS employee_name,
           A.loc AS location,
           'America' country,
           date_col TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 
           FROM existing_table_name;

CodePudding user response：

The CREATE TABLE ... AS SELECT ... is not intended for adding new behaviors to the new table. Changes such as adding a default value to a column should happen in a separate ALTER statement. Therefore, use this approach:

CREATE TABLE IF NOT EXISTS new_table_name AS
SELECT employee,
       location,
       'America',
       date_col,
FROM existing_table_name;

ALTER TABLE new_table_name
MODIFY COLUMN date_col TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

If you intended those aliases to be instructions to change the column names, then you'll need ALTER statements for those too:

ALTER TABLE new_table_name 
RENAME COLUMN employee TO employee_name;