Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

I have a table with an 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 to table tweets*/    FOREIGN KEY (tweet_id) references tweets(tweet_id) ); 

I wish to populate this table from parts of another table:

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

I get an error which says the number of columns do not match up, which is because of the primary key (which is an auto-incremented value and thus does not need to be set). How do I get around this?

like image 851
CodeKingPlusPlus Avatar asked Jul 21 '13 15:07

CodeKingPlusPlus


People also ask

How can I get auto increment value after insert?

To obtain the value immediately after an INSERT , use a SELECT query with the LAST_INSERT_ID() function. For example, using Connector/ODBC you would execute two separate statements, the INSERT statement and the SELECT query to obtain the auto-increment value.

Can we insert auto increment value in MySQL?

Syntax for MySQLMySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature. By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record. VALUES ('Lars','Monsen'); The SQL statement above would insert a new record into the "Persons" table.

How do you get the last ID from a table if it's set to auto increment?

To get the next auto increment id in MySQL, we can use the function last_insert_id() from MySQL or auto_increment with SELECT. Creating a table, with “id” as auto-increment.

Can we have 2 auto increment in MySQL?

You can't have two auto-increment columns.


2 Answers

You need to explicitly list the columns in the insert statement:

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

Also, I think it is better form to use explicit joins, rather than nested selects:

insert into 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     from 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     where rt_user_id != -1; 

This often (but not always) helps the optimizer find the best query plan.

like image 92
Gordon Linoff Avatar answered Sep 28 '22 05:09

Gordon Linoff


You simply set the primary key to NULL during the insert.

INSERT INTO rt_table  SELECT    NULL,   rt_user_id,   (SELECT      user_name    FROM     users u    WHERE u.user_id = t.rt_user_id),   source_user_id,   (SELECT      user_name    FROM     users u    WHERE u.user_id = t.source_user_id),   tweet_id  FROM   tweets t  WHERE rt_user_id != - 1 ; 
like image 24
Styphon Avatar answered Sep 28 '22 05:09

Styphon