MySQL How do you INSERT INTO a table with a SELECT subquery returning multiple rows?

Question

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

Foliole

You can use it with a procedure using a loop for all items to be inserted. – Parboil

I'm a MySQL newb, how do you iterate over a result? – Foliole

dev.mysql.com/doc/refman/5.1/en/insert-select.html – Intramundane

@Intramundane No, that wont work. I do indeed want all results inserted into this table. Not just one – Foliole

@Gah_Jamn-it The INSERT ... SELECT syntax works for multiple rows too. – Intramundane

Why am I getting ERROR 1242 (21000): Subquery returns more than 1 row then? – Foliole

@Gah_Jamn-it I don't know. (Old version of MySQL?) It works for me. You can see example query of this kind in Melissa's answer. – Intramundane

Answer 1

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.

Diphosgene

What if I have multiple names? Not only Henry and I need iterate over them. – Lecroy

Works for me! Thank you. – Seel

Thanks for the clear answer, @Ryan. I also want to bring to the attention of anyone who may have gotten here for the same reason as I did, that there is no need for a VALUES clause that encompasses the SELECT statement. I kept getting errors in MySQL because I was using INSERT INTO table(column) VALUES(SELECT col FROM table). – Littleton

Answer 2

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.

Splendid

Answer 3

  INSERT INTO Results
    (
     People,
     names,
    )
    SELECT d.id, 'Henry'
    FROM Names f
    JOIN People d ON d.id  = f.id

Forgather

That doest work decoding.wordpress.com/2008/10/11/… – Foliole

Answer 4

INSERT INTO Results
    (
     People,
     names,
    )
    VALUES
    (
     (
       SELECT d.id
       FROM Names f
       JOIN People d ON (d.id  = f.id) limit 1
     ),

     (
      "Henry"
     ),
    );

Geophysics

Welcome to Stack Overflow! Rather than only post a block of code, please explain why this code solves the problem posed. Without an explanation, this is not an answer. – Rocco

This doesn't really answer the question. It was "[...] populate the new table with all results returning from this subquery", and this limits the subquery results to one row. (I know this answer is over 4 years old but figured I'd point this out anyway.) – Hookworm

Answer 5

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

Opheliaophelie

Answer 6

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 )

Disturbed

Answer 7

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.

Chantress