MySQL: SQL insert query to duplicate data in same table

Question

I have a table say table1(id, col2, col3), and I want to duplicate all data of id 1 but with different id say 11 (id is not auto generated column). I wrote the following sql query which is not working for me (giving syntax error):

INSERT INTO table1(
id,
col2,
col3
)
VALUES (

SELECT 11 , col2, col3
FROM table1 WHERE id=1
)

And in what way has this not worked? What error did you get? — BugFinder
– BugFinder, Commented Sep 3, 2012 at 11:11
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax — gmuhammad
– gmuhammad, Commented Sep 3, 2012 at 11:13
The problem is : don't put the "VALUES" keyword" when using insert... select — Raphaël Althaus
– Raphaël Althaus, Commented Sep 3, 2012 at 11:16
removed the VALUES keyword and put the id condition but still not working. Now getting the error #1054 - Unknown column 'col2' in 'field list' — gmuhammad
– gmuhammad, Commented Sep 3, 2012 at 11:22

Raphaël Althaus · Accepted Answer · 2012-09-03 11:34:05Z

10

Don't use the "VALUES" keyword

INSERT INTO table1(
id,
col2,
col3
)


SELECT 11 , col2, col3
FROM table1
WHERE id = 1

EDIT :

Check if you're working with the right column names :

DESC table1;

edited Sep 3, 2012 at 11:34

answered Sep 3, 2012 at 11:13

Raphaël Althaus

60.6k6 gold badges106 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

davidethell Over a year ago

+1, the only one that mentioned you need the where clause of id = 1 to make this work.

gmuhammad Over a year ago

removed the VALUES keyword but still not working. Now getting the error #1054 - Unknown column 'col2' in 'field list'

Raphaël Althaus Over a year ago

@gmuhammad what happens if you write select 11, col2, col3 from table1 ?

gmuhammad Over a year ago

getting the same error #1054 - Unknown column 'col2' in 'field list'

Raphaël Althaus Over a year ago

@gmuhammad then what gives DESC table1;

|

András Ottó · Accepted Answer · 2012-09-03 11:14:45Z

3

Try this:

INSERT INTO table1(
id,
col2,
col3
)
SELECT 11 , col2, col3
FROM table1
WHERE table1.id = 1

OR if you need more something like this:

INSERT INTO table1(id, col2, col3)
SELECT (SELECT MAX(id) FROM table1) + 1 , col2, col3
FROM table1

answered Sep 3, 2012 at 11:14

András Ottó

7,7351 gold badge30 silver badges38 bronze badges

Comments

Mahmoud Gamal · Accepted Answer · 2012-09-03 11:11:32Z

2

Use INSERT INTO ... SELECT with WHERE id = 1:

INSERT INTO table1(id, col2, col3)
SELECT '1' + id , col2, col3
FROM table1
WHERE id = 1

answered Sep 3, 2012 at 11:11

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

Comments

podiluska · Accepted Answer · 2012-09-03 11:12:33Z

2

INSERT takes VALUES or SELECT, not both.

INSERT INTO table1( id, col2, col3 ) 
SELECT 11 , col2, col3 
FROM table1 
WHERE id = 1

answered Sep 3, 2012 at 11:12

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Collectives™ on Stack Overflow

MySQL: SQL insert query to duplicate data in same table

4 Answers 4

6 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related