CODEWITHSUNDEEP
mysql
jeffery_the_wind
jeffery_the_wind

Reputation: 18238

MySQL INSERT into select * from with default timestamp column

This is simple problem, but I feel like there should be an elegant solution. I have had a script which works by using this statement:

insert into table1 select * from table2 where pk = "'.$pk.'";

Recently I want to add a column to the end of table1 which is a timestamp column with default value now(). This generally works and I was expecting to not have to change the above statement, but if I add the new timestamp column, the above statement will fail with the following error:

column count doesn't match value count

Upvotes: 1

Views: 718

Answers (2)

jeffery_the_wind
jeffery_the_wind

Reputation: 18238

Sorry, often happens that as soon as I ask my question here on stackoverflow I end up trying something that works. I was able to alter the statement in the question to the following which works, although I lose the default functionality for the new column. Since the last column is hard-coded in this case it still works the same way.

insert into table1 select *, now() from table2 where pk = "'.$pk.'";

Upvotes: 0

Rakesh Kumar
Rakesh Kumar

Reputation: 4430

Here are 2 solution for it, either specify your columns list

insert into table1 (col1, col2) select col1, col2  from table2 where pk = "'.$pk.'";

or

create same column in table1 to avoid column list in query

Upvotes: 1

Related Questions