Solved

Insert select in mySQL

Posted on 2011-02-22
13
281 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 55

Expert Comment

by:Huseyin KAHRAMAN
ID: 34953141
try

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

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

Expert Comment

by:Huseyin KAHRAMAN
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 55

Expert Comment

by:Huseyin KAHRAMAN
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 55

Expert Comment

by:Huseyin KAHRAMAN
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 55

Expert Comment

by:Huseyin KAHRAMAN
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 55

Expert Comment

by:Huseyin KAHRAMAN
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 55

Accepted Solution

by:
Huseyin KAHRAMAN earned 500 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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…

688 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