Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Insert select in mySQL

Posted on 2011-02-22
13
Medium Priority
?
284 Views
Last Modified: 2012-05-11
When I try to run this insert statement in mySQL, either from a command prompt or inside of a stored procedure, I get the following error:

Subquery returns more than 1 row.

Any ideas?  There is no subquery.

insert into ContestantEventIndvScores
(select distinct ce.ContestantId,
ce.ContestantFirstName,
ce.ContestantLastName,
j.Id,
j.LastName,
ce.EventId,
ce.EventName,
s.Id,
s.ScoreName,
NULL from EventContestants ce, IndivScores s, EventCriteria ec, Judge j
where ce.Eventid = ec.EventId and ec.IndivScoreid = s.Id
and j.Id is not null);
0
Comment
Question by:oedepus
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 60

Expert Comment

by:HainKurt
ID: 34953141
try

insert into ContestantEventIndvScores
select distinct ce.ContestantId,
...
and j.Id is not null;

ie, remove "(" and ")"
0
 
LVL 60

Expert Comment

by:HainKurt
ID: 34953150
is ContestantEventIndvScores a table?
0
 

Author Comment

by:oedepus
ID: 34953656
Yes it is a table.  All of the entities in the query are tables.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 60

Expert Comment

by:HainKurt
ID: 34953796
did you try removing "(" & ")" around select?
0
 

Author Comment

by:oedepus
ID: 34955060
Yes I did that.  The interesting thing is that I fully expect the query to return more than 1 row.

In oracle you can run these types of insert statements as block operations with no problem.

Am I missing some nuance of mySQL?
0
 
LVL 60

Expert Comment

by:HainKurt
ID: 34955113
it may return multiple records and the part "(select ....)" is your subquery...
deistinct here does not enough to give you one record... did it work after removing ( & )
0
 

Author Comment

by:oedepus
ID: 34955416
I dont want just one record, I want all of the records returned by the query to be inserted, and no removing the parenthesis did not work.

I will remove distinct and see if that works.

Thanks.
0
 

Author Comment

by:oedepus
ID: 34955423
Nope that did not work either.
0
 
LVL 60

Expert Comment

by:HainKurt
ID: 34955775
try adding column names

insert into ContestantEventIndvScores (ContestantId, ContestantFirstName, ..., nullColumn)
(
select distinct ce.ContestantId,
...
from EventContestants ce, IndivScores s, EventCriteria ec, Judge j
where ce.Eventid = ec.EventId and ec.IndivScoreid = s.Id and j.Id is not null
)

or without (..)

insert into ContestantEventIndvScores (ContestantId, ContestantFirstName, ..., nullColumn)
select distinct ce.ContestantId,
...
from EventContestants ce, IndivScores s, EventCriteria ec, Judge j
where ce.Eventid = ec.EventId and ec.IndivScoreid = s.Id and j.Id is not null

looks like you miss one join (table Judge is not joined to existing tables)
0
 
LVL 60

Expert Comment

by:HainKurt
ID: 34955790
what do you get when you run the inner query?

select distinct ce.ContestantId,
...
from EventContestants ce, IndivScores s, EventCriteria ec, Judge j
where ce.Eventid = ec.EventId and ec.IndivScoreid = s.Id and j.Id is not null
0
 

Author Comment

by:oedepus
ID: 34955863
I got the record set that I am looking for, no duplicate rows, and no subquery.
0
 
LVL 60

Accepted Solution

by:
HainKurt earned 2000 total points
ID: 34956047
:) ok one more thing... do you have any trigger on ContestantEventIndvScores table? if yes, disable them first and run this insert again
then enable it...
if thats the cause please close this problem and open a new post for trigger problem...
0
 

Author Comment

by:oedepus
ID: 34956237
yeah, the trigger was the problem.

Thanks.  Closing now.

Awarding points.

I feel silly.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question