MySQL INSERT into select * from with default timestamp column

Question

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

insert into jobticket.flatdestroyed (col1,col2) select col1,col2,..... So select the columns other than the timestamp on and insert into should have the columns other than the timestamp and if its set default as current timestamp it should do what you want. — Abhik Chakraborty
– Abhik Chakraborty, Commented Aug 2, 2016 at 11:36
@AbhikChakraborty yes thank you I was hoping not to have to do that, but I can see that your solution will work. Also sorry I just edited the Q, it is table1 that I want to add the column to. Only reason is there are like 100 columns... guess I'm just being lazy. — jeffery_the_wind
– jeffery_the_wind, Commented Aug 2, 2016 at 11:39
So you showed us the statement that works, but didn't show us the statement that didn't work? And you want help with the statement that didn't work? o.O — Lightness Races in Orbit
– Lightness Races in Orbit, Commented Aug 2, 2016 at 11:48

Rakesh Kumar · Accepted Answer · 2016-08-02 11:41:54Z

1

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

answered Aug 2, 2016 at 11:41

Rakesh Kumar

4,4522 gold badges19 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jeffery_the_wind · Accepted Answer · 2016-08-02 11:54:05Z

0

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.'";

answered Aug 2, 2016 at 11:54

jeffery_the_wind

18.3k35 gold badges109 silver badges165 bronze badges

Collectives™ on Stack Overflow

MySQL INSERT into select * from with default timestamp column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related