78

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

stackoverflow
  • 18,348
  • 50
  • 129
  • 196

7 Answers7

148
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.

Ryan
  • 26,884
  • 9
  • 56
  • 83
  • 19
    What if I have multiple names? Not only `Henry` and I need _iterate_ over them. – Anton Dozortsev Sep 21 '17 at 07:08
  • 1
    Works for me! Thank you. – Dan Nissenbaum May 01 '19 at 02:30
  • 1
    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). – Alain Jun 10 '20 at 05:14
15

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.

MiggityMac
  • 919
  • 9
  • 11
12
  INSERT INTO Results
    (
     People,
     names,
    )
    SELECT d.id, 'Henry'
    FROM Names f
    JOIN People d ON d.id  = f.id
triclosan
  • 5,578
  • 6
  • 26
  • 50
10
INSERT INTO Results
    (
     People,
     names,
    )
    VALUES
    (
     (
       SELECT d.id
       FROM Names f
       JOIN People d ON (d.id  = f.id) limit 1
     ),

     (
      "Henry"
     ),
    );
nickhar
  • 19,981
  • 12
  • 60
  • 73
Umar Enesi Ibrahim
  • 149
  • 1
  • 2
  • 11
    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. – Martijn Pieters Nov 08 '12 at 12:27
  • 3
    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.) – MJV Oct 18 '16 at 06:59
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

Sveteek
  • 161
  • 1
  • 6
1

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 )
Fabio_M
  • 879
  • 7
  • 21
-1

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.

sahmad
  • 11