Insert...select query

[vincekaribusana]

Hi,

I need to duplicate some rows in the database, so I tried to use the following query:

INSERT INTO table (col1, col2, col3, col4) VALUES (:newCol1, (SELECT col2, col3, col4 FROM table WHERE col1 = :oldCol1))

But i get the following error:

Error Code: 1136. Column count doesn’t match value count at row 1

Not sure how to achieve this with only one query.

Many thanks

[sibertius]

@vincekaribusana

INSERT INTO table (col1, col2, col3, col4) VALUES (:newCol1, (SELECT col2, col3, col4 FROM table WHERE col1 = :oldCol1))

CTE like this should work in modern MySQL:

WITH list as (SELECT cols FROM table WHERE id=?)
INSERT INTO table (cols) VALUES (SELECT cols FROM list)

[Martyr2]

Try…

INSERT INTO table (col1, col2, col3, col4) select :newCol1, col2, col3, col4 FROM table WHERE col1 = :oldCol1

Now I am not sure if the placeholders will work just like this, but the format of this query is what you should be after. Notice that we don’t include ‘VALUES’ clause and such.

[igor_g]

MySQL :: MySQL 8.0 Reference Manual :: 13.2.6.1 INSERT … SELECT Statement