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

Question

I am trying to create an insert statement, below is what I have so far:

INSERT INTO Role (application, developer, role) 

VALUES (? , 
(SELECT Id from Developer WHERE firstName='Alice' and lastname='Wonderland'), 
'ARCHITECT');

In the missing value field, I am trying to do a join, the following join statement returns the VALUES I need:

   SELECT a.id 
FROM Application a
JOIN application d ON d.id = a.id and a.category='GAMES'

However, when I insert the previous query into the missing value field in the insert statement, it gives me an error stating that the query returned more than 1 row (which is intentional, seeing as I want ALL rows returned to be inserted into the role table)

Can anyone provide me feedback on how to fix this issue?

You can only insert one row at a time, so you'll need to find a way to do this one-by-one. — AdamMc331
– AdamMc331, Commented Oct 20, 2014 at 15:09
That join you have makes no sense. You are joining the same table on the same id column. Are you sure this is correct? — Donal
– Donal, Commented Oct 20, 2014 at 15:10
@Donal, yes the join statement works fine outside of the value field. If you have a better way to setup the join statement, let me know. — user3220348
– user3220348, Commented Oct 20, 2014 at 15:12
@McAdam331: you can insert more than one row at a time. Either by using insert into ... values (1,1), (2,2) or by using insert into ... select ... — user330315
– user330315, Commented Oct 20, 2014 at 15:13
@a_horse_with_no_name, good catch. My mistake. Also, OP, I think Donal's question is, there's no need to join a table with itself. Why not just select from that table? A simpler way to get all GAMES applications would be: SELECT id FROM application WHERE category = 'GAMES'; — AdamMc331
– AdamMc331, Commented Oct 20, 2014 at 15:16

Gordon Linoff · Accepted Answer · 2014-10-20 15:17:25Z

1

I think this is what you are looking for:

INSERT INTO Role(application, developer, role)
    SELECT a.id, (SELECT Id from Developer WHERE firstName = 'Alice' and lastname = 'Wonderland'),
           'ARCHITECT' 
    FROM Application a JOIN
         application d
         ON d.id = a.id and a.category = 'GAMES';

Note: I was going to move the subquery with Developer into the FROM clause. However, if there are no matches, this will return NULL -- which seems to be your intention. If I put it in the FROM clause, you will get no matches (MySQL doesn't have a left cross join).

answered Oct 20, 2014 at 15:17

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chris Stillwell · Accepted Answer · 2014-10-20 15:12:41Z

0

Incorporate your other parameters into your select statement

INSERT INTO Role (application, developer, role)
SELECT ?, Id, 'ARCHITECT' FROM Application a 
JOIN application d ON d.id = a.id and a.category='GAMES'

answered Oct 20, 2014 at 15:12

Chris Stillwell

10.6k10 gold badges74 silver badges82 bronze badges

2 Comments

user3220348 Over a year ago

How would this look? (the SQL statement) I don't get how to create an insert statement that handles every element I need, as well as the join without using a value field.

Chris Stillwell Over a year ago

Just like this. An INSERT INTO with a SELECT statement after it will insert all the rows returned by the SELECT

Rahul · Accepted Answer · 2014-10-20 15:15:41Z

0

You need to use INSERT INTO .. SELECT FROM construct to get this done. A mock up query would look like below

INSERT INTO Role (application, developer, role)    
select a.id,
dev.Id,
'ARCHITECT'
FROM Application a
JOIN developer dev on a.Id = dev.Id
WHERE a.category='GAMES'
AND dev.firstName='Alice' 
and dev.lastname='Wonderland'

answered Oct 20, 2014 at 15:15

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

2 Comments

user3220348 Over a year ago

In this case how would i set the value of "role"? Its a string. Also, the developer's first name and last name are used to obtain its developer id.

Rahul Over a year ago

Follow along the answer. Hope that helps.

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related