CODEWITHSUNDEEP
mysqlsql
Azima
Azima

Reputation: 4141

set a value to column in one table with another table's column data

I want to create a duplicate table of, say, 'users', add few columns to new table 'users_history' and in one of the newly added column, I want to set the source table's column value.

Here's the query:

CREATE TABLE db_portal.users_history LIKE db_portal.users;INSERT db_portal.users_history SELECT * FROM db_portal.users;

ALTER TABLE db_portal.users_history MODIFY COLUMN id int(11) NOT NULL, 
   DROP PRIMARY KEY, ENGINE = MyISAM, ADD action VARCHAR(8) DEFAULT 'insert' FIRST, 
   ADD revision INT(6) NOT NULL AUTO_INCREMENT AFTER action,
   ADD dt_datetime DATETIME NOT NULL DEFAULT (SELECT join_date FROM db_portal.users) AFTER revision,
   ADD PRIMARY KEY (id, revision);

In newly added column, dt_datetime, I want to set value of join_datecolumn from origin table.

But I guess this is not working for me. Any help is very much appreciated. Thanks.

Upvotes: 1

Views: 39

Answers (1)

krokodilko
krokodilko

Reputation: 36097

Do it in a different order:

CREATE TABLE db_portal.users_history LIKE db_portal.users;

ALTER TABLE db_portal.users_history MODIFY ..........

/* List all columns from "old" table + one new column from "new" table */    
INSERT db_portal.users_history( Old_col1, Old_col2, .... Old_colN, dt_datetime )
SELECT t.*,
       join_date
FROM db_portal.users t;

In this way the last column in the insert statement: dt_datetime is filled with values of join_date.

Upvotes: 1

Related Questions