MySQL How do you INSERT INTO a table with a SELECT subquery returning multiple rows?
MySQL How do you INSERT INTO a table with a SELECT subquery returning multiple rows?
INSERT INTO Results
(
People,
names,
)
VALUES
(
(
SELECT d.id
FROM Names f
JOIN People d ON d.id = f.id
),
(
"Henry"
),
);
I WANT to populate the new table with all results returning from this subquery. How do I do this without getting a ERROR 1242 (21000): Subquery returns more than 1 row
INSERT INTO Results (People, names )
SELECT d.id, 'Henry'
FROM Names f
JOIN People d ON d.id = f.id
Combine the static string Henry
with your SELECT
query.
Here is what I've found that works well. It is a little long but many times extra data needs to be shuffled around.
Insert multiple rows into table1 from table2 with values. EXAMPLES:
INSERT INTO table1 (col1, col2, col3, col4, col5)
SELECT col1,col2,col3,col4,col5
FROM table2 t2
WHERE t2.val2 IN (MULTIPLE VALUES)
AND (Another Conditional);
You can insert hard coded values to get insert multiple rows with repeat data:
INSERT INTO table1 (col1, col2, col3, col4, col5)
SELECT "Value", col2, col3, "1900-01-01","9999-12-31"
FROM table2 t2
WHERE t2.val2 IN (MULTIPLE VALUES)
AND (Another Conditional);
Note that: "Value","1900-01-01","9999-12-31" will repeat across all rows inserted.