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?
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.
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.
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.
You can't have two auto-increment columns.
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.
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 ;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With