MySql: How do I insert multiple columns from multiple rows into another table?-CodePudding

I want to copy multiple columns (not all) from multiple rows in source_table, and create a new row in destination_table for each set of values from a single row.
So in this example, 5 new rows should be created in destination_table, each with three of their fields set:

INSERT INTO destination_table (source_id, foo_id, bar) 
VALUES( 
    (SELECT id, foo_id, bar FROM source_table WHERE id=399274),
    (SELECT id, foo_id, bar FROM source_table WHERE id=399279),
    (SELECT id, foo_id, bar FROM source_table WHERE id=380409), 
    (SELECT id, foo_id, bar FROM source_table WHERE id=400037), 
    (SELECT id, foo_id, bar FROM source_table WHERE id=401026)
);

but this results in an error: ERROR 1241 (21000): Operand should contain 1 column(s)

I have perused many articles which deal with similar questions but cannot find an answer to this specifically.

CodePudding user response：

Instead of the insert...values syntax, use insert...select:

INSERT INTO destination_table (source_id, foo_id, bar) 
SELECT id, foo_id, bar FROM source_table
WHERE id in (399274,399279,380409,400037,401026);