Solved

Insert select in mySQL

Posted on 2011-02-22
13
266 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
  • 7
  • 6
13 Comments
 
LVL 51

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 51

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
 
LVL 51

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 51

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 51

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 51

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 51

Accepted Solution

by:
HainKurt 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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now