select - MySQL insert into table with auto-increment while selecting from another table -

i have table auto-increment primary key:

create table rt_table (   rtid int primary key auto_increment,    rt_user_id bigint,               /*user being retweeted*/   rt_user_name varchar(70),        /*user name of rt_user_id*/   source_user_id bigint,           /*user tweeting rt_user_id*/   source_user_name varchar(70),    /*user name of source_user_id*/   tweet_id bigint,                 /*fk table tweets*/    foreign key (tweet_id) references tweets(tweet_id) ); 

i wish populate table parts of table:

insert rt_table  select rt_user_id, (select user_name users u u.user_id = t.rt_user_id),        source_user_id, (select user_name users u u.user_id = t.source_user_id),        tweet_id   tweets t  rt_user_id != -1; 

i error says number of columns not match up, because of primary key (which auto-incremented value , not need set). how around this?

you need explicitly list columns in insert statement:

insert rt_table (rt_user_id, rt_user_name, source_user_id, source_user_name, tweet_id) select rt_user_id, (select user_name users u u.user_id = t.rt_user_id),        source_user_id, (select user_name users u u.user_id = t.source_user_id),        tweet_id   tweets t  rt_user_id != -1; 

also, think better form use explicit joins, rather nested selects:

insert rt_table (rt_user_id, rt_user_name, source_user_id, source_user_name, tweet_id)     select t.rt_user_id, u.user_name, t.source_user_id, su.user_name, t.tweet_id     tweets t left outer join          users u          on t.rt_user_id = u.user_id left outer join          users su          on t.source_user_id = su.user_id     rt_user_id != -1; 

this (but not always) helps optimizer find best query plan.