CODEWITHSUNDEEP
mysql
User12345
User12345

Reputation: 5480

Insert from multiple tables gives duplicate entry error

I have 2 tables in mysql named a and b. Both of them have a unique column id. Now I want to create a table abc that has id as autoincrement column by inserting records from both the tables a and b.

Table a

1 sam
2 ram
3 tim


table b 

1 tom
2 sun
3 jim

Desired result

table abc

1 sam
2 ram
3 tim
4 tom
5 sun
6 jim

I have tried like below

insert into table abc select * from a

This statement runs succesfully

insert into table abc select * from b`

This statement fails saying duplicate entry for primary

How can I achieve my desired result

Upvotes: 0

Views: 37

Answers (1)

Paul Spiegel
Paul Spiegel

Reputation: 31772

Let the AUTO_INCREMENT column in the abc table create its own values. Don't select and insert the id column from other tables.

insert into table abc(names) select names from a order by id;
insert into table abc(names) select names from b order by id;

Upvotes: 1

Related Questions