copy mysql table into another table (within the same database) and auto increment inserted rows

Question

I have two tables listed below:

Table: "wp_topic"

Table: "wp_default_topics"

What i am trying to do is copy all of the rows from wp_default_topics (except for ID) to wp_topic auto-increment from the new table (wp_default_topics).

I can use the below code to copy everything fine:

INSERT INTO wp_topic SELECT * FROM wp_default_topics

but i will end up wit the error "#1062 - Duplicate entry '28' for key 'PRIMARY'"

I have tried:

INSERT INTO wp_topic SELECT * FROM wp_default_topics ON DUPLICATE KEY UPDATE ID=VALUES(ID+1)

But end up with the error message:

"#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '+1)' at line 1"

and i have tried:

INSERT INTO wp_topic SELECT * FROM wp_default_topics ON DUPLICATE KEY UPDATE LAST_INSERT_ID(wp_topic.ID)

But i end up with the error message:

"#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(wp_topic.ID)' at line 1"

I have also tried the below which i thought would work but unfortunately it does not.

INSERT INTO wp_topic SELECT (user_id, name, subject, company, date) FROM wp_default_topics

"#1241 - Operand should contain 1 column(s)"

I have tried a few variations of the above but without any luck could i get a pointer as to what i am missing here?

Thank You

David

If you actually list out the fields in your insert and select, omitting the id field; you shouldn't have a problem. — Uueerdo, Jan 26 '17 at 23:15

score 3 · Accepted Answer · answered Jan 26 '17 at 23:16

3

You need to skip the id column when inserting, so you have to list all the columns explicitly, not use SELECT *

INSERT INTO wp_topic (user_id, name, subject, company, date)
SELECT user_id, name, subject, company, date
FROM wp_default_topics

answered Jan 26 '17 at 23:16

Barmar

741,623
53
500
612

Just opened the question, and you answered it perfectly. – Xorifelse Jan 26 '17 at 23:16
Yes thanks Barmar that does work and i see what i was doing wrong. – Sandy Jan 26 '17 at 23:19

score 0 · Answer 2 · answered Jan 26 '17 at 23:19

0

Can't you just remove the id field from the select and insert?

INSERT INTO wp_topic (`user_id`, `name`, `subject`, `company`, `date`) 
SELECT `user_id`, `name`, `subject`, `company`, `date` FROM wp_default_topics

answered Jan 26 '17 at 23:19

johnc

76
3

a second question i notice you use the ` around the column names (user_id, name etc) but Barmar does not, i have not noticed the difference in the code, but does this have an effect if i use them or not ? – Sandy Jan 26 '17 at 23:21
Normally the query will run the same either way. This post has some good info on it: http://stackoverflow.com/questions/261455/using-backticks-around-field-names – johnc Mar 08 '17 at 22:56

score 0 · Answer 3 · answered Jan 26 '17 at 23:47

0

Another slightly simpler way of writing the INSERT sub-select query that's been suggested already:

INSERT INTO wp_topic
SELECT NULL, user_id, name, subject, company, date FROM wp_default_topics;

answered Jan 26 '17 at 23:47

James C

14,047
1
34
43

copy mysql table into another table (within the same database) and auto increment inserted rows

3 Answers3