r0xette r0xette - 1 month ago 5
MySQL Question

copy data from one table to another where column count is different

I have two tables

table_1
-------
id,name,address

table_2
-------
id,name,address,phone_no


I want to copy all data from table_1 to table_2. When I execute the following query it throws an error.

mysql> insert into table_2 select * from table_1;
ERROR 1136 (21S01): Column count doesn't match value count at row 1

Answer

Specify the columns:

insert into table_2 (id, name, address) select (id, name, address) from table_1

Of course, the id needs to be insertable (not a generated value) and phone_no needs to allow null values (otherwise you'd have to specify a default value).