MySQL How do you INSERT INTO a table with a SELECT subquery returning multiple rows?
MysqlSelectSubqueryInsert IntoMysql Problem Overview
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
Mysql Solutions
Solution 1 - Mysql
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.
Solution 2 - Mysql
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.
Solution 3 - Mysql
INSERT INTO Results
(
People,
names,
)
SELECT d.id, 'Henry'
FROM Names f
JOIN People d ON d.id = f.id
Solution 4 - Mysql
INSERT INTO Results
(
People,
names,
)
VALUES
(
(
SELECT d.id
FROM Names f
JOIN People d ON (d.id = f.id) limit 1
),
(
"Henry"
),
);
Solution 5 - Mysql
The reason of this error (Subquery returns more than 1 row) is that you use parenthesis (). Look more careful to the best answer. It doesn't contain parethesis around subquery
Solution 6 - Mysql
In MySql multiple values from strings can be inserted like the following avoiding duplicates. Thanks.
insert into brand(name) select * from (
select 'Fender' as name
union select 'a'
union ..... ) t
where not exists (select 1 from brand t2 where t2.name COLLATE latin1_general_ci = t.name COLLATE utf8mb4_unicode_ci )
Solution 7 - Mysql
insert into ec_element(parentid,name) select elementid , 'STARTUP' from ec_element where name = 'BG';
insert statement takes values elementid from the table found with condition fulfilled and a label string.