Solved

Insert select in mySQL

Posted on 2011-02-22
13
267 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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…
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

939 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

8 Experts available now in Live!

Get 1:1 Help Now