Insert into table from another with different columns count (MySQL)

Question

Question, why this dose not work?

create table one (a int(1) default 1, b int(2));

create table two (b int(1));

insert into one select * from two;

error:

Column count doesn't match value count at row 1

a know it, a can count, but why, philosophically? database knows, what the name of inserting column from table two is b, knows that the column a in table one has a default value equal 1.. so, what problem of executing this query?

And general - How can i do this differently, not manual, without information of a columns and their count, if this way is impossible?

I know this: table two always have all the same columns, that the table one have. But table one have another columns too, that have a some default values.

Is there some way to do that? insert all data from two in one, and fill the remaining columns by some default or other values!

Need help! Thank you very match!

insert into one(b) select b from two;

SIDU
– SIDU

2016-07-28 22:56:27 +00:00
Commented Jul 28, 2016 at 22:56 — SIDU
– SIDU, Commented Jul 28, 2016 at 22:56

Gordon Linoff · Accepted Answer · 2016-07-28 22:55:35Z

4

When you run:

insert into one
    select * from two;

The SQL engine automatically puts in the columns that are implied.

For the insert, this is the list of columns in declaration order.
For the *, this is the list of columns in declaration order.

There is no "matching" of columns by names, only lists of columns in each table.

So, the query is really:

insert into one(a, b)
    select b from two;

That looks like an error to me.

Moral of the story? Write that code that you intend. Always include columns lists, particularly for insert statements. So write:

insert into one(b)
    select b from two;

answered Jul 28, 2016 at 22:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

dmitryck Over a year ago

Thank you. I understand that. But this means, that i must always know the name of the same columns, that must be inserting automaticly in my opinion, because both tables have them. Why i must have set this names manualy? ok... it is very strange for relational theory of database.. and eventually - so, this task can not be solved in SQL? Maybe some easy way with minimal programming is and maximum SQL? Because name of columns - i did't know them. I know that the table one have all the same columns that table two, and some more columns with default values. Thank you!

dmitryck Over a year ago

PS. I have done this task with some additional programming, thanx for answer insert into one(b) select b from two;!

Collectives™ on Stack Overflow

Insert into table from another with different columns count (MySQL)

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related