Solved

Insert select in mySQL

Posted on 2011-02-22
13
268 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
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.

 
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
 

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

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.

Question has a verified solution.

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

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

809 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