mysql INSERT INTO ... SELECT

Question

I want to select one row in a table and insert it into another table like this:

INSERT INTO  positionenneu
select *
from positionenneu_0603 where belegnummer like '4106192%'

I got an error because of duplicating id values. In both tables is id primary key AUTO_INCREMENT. How can I do this without id column? Thank you for the answer. Have a nice day!

don't select *, then. select all,the,fields,except,the,ones,you,dont,want ... — Marc B
– Marc B, Commented Mar 6, 2014 at 15:21

lc. · Accepted Answer · 2014-03-06 15:15:13Z

1

Don't use the * and specify the column list instead, naturally omitting the id column:

INSERT INTO  positionenneu (foo, bar, baz)
select foo, bar, baz
from positionenneu_0603 where belegnummer like '4106192%'

Or you can of course use the id column somewhere if you need:

INSERT INTO  positionenneu (foo, bar, baz, positionenneu_0603_id)
select foo, bar, baz, id
from positionenneu_0603 where belegnummer like '4106192%'

answered Mar 6, 2014 at 15:15

lc.

117k21 gold badges164 silver badges190 bronze badges

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

Comments

Jim · Accepted Answer · 2014-03-06 15:15:05Z

0

It's good practice to specify the columns that you are inserting. This also allows you to skip the id field:

INSERT INTO  positionenneu (field1,field2,field3)
    select field1_0603, field2_0603, field3_0603
    from positionenneu_0603 where belegnummer like '4106192%'

answered Mar 6, 2014 at 15:15

Jim

22.7k6 gold badges55 silver badges81 bronze badges

Collectives™ on Stack Overflow

mysql INSERT INTO ... SELECT

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