mitedu mitedu - 5 months ago 19
MySQL Question

Atuto increment column does not increses Id numer in mysql table

Why auto incremnt column does not increases id number in

Insert into from other mysql table
query ? Here is my query:

insert into tbl_1 (pk, col_a, col_b, col_c)
select null,col_a, col_b, col_c from tbl_2


pk
column in
tbl_1
always returns same number i.e. '1', '1', '1'..... Can anyone tell me what is value I should select for auto increment column so that it will return '1', '2', '3',....and so on. I have large number of records in
tbl_2
and doesn't have
pk
column. It might be simple question but I am not able to solve it.

Answer Source

Change your insert query,

insert into tbl_1 (pk, col_a, col_b, col_c)
select null,col_a, col_b, col_c from tbl_2

to

insert into tbl_1 (col_a, col_b, col_c) 
select col_a, col_b, col_c from tbl_2 

You don't need to specify the auto increment column while inserting.

For bulk insert auto increment will not work. You have to use diff method for that. For you try this query,

insert into tbl_1 (pk, col_a, col_b, col_c)
select @curRank := @curRank + 1 AS rank,col_a, col_b, col_c from tbl_2, (SELECT @curRank := 0) rn order by col_a

Hope it will help.