This article looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Course Of The Month
11 days, 13 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Using a SELECT with Subquery as a source for an INSERT.
Posted on 2013-05-10
I have a rather convoluted query to drill down through several tables and return a distinct list of values (in this case ontractor Id's)
The query is as follows:
It produces a lovely unique list of ContractId's
I now need to do an insert based upon these results that I thought might look something like this:
Theat just results in a "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated." message.
What am I doing wrong here?
Regards
The query is as follows:
SELECT DISTINCT Contractor.ContractorId
FROM Contractor
INNER JOIN ContractorContainer ON Contractor.ContractorID = ContractorContainer.ContractorId
WHERE ContractorContainer.ContainerId IN
(SELECT Container.ContainerID
FROM Container
INNER JOIN ContainerSizeResolve ON Container.ContainerID = ContainerSizeResolve.ContainerId
INNER JOIN WasteType ON ContainerSizeResolve.WasteTypeId = WasteType.WasteTypeId
INNER JOIN WasteSubTypeResolve ON WasteType.WasteTypeId = WasteSubTypeResolve.WasteTypeId
INNER JOIN WasteSubType ON WasteSubTypeResolve.WasteSubTypeId = WasteSubType.WasteSubTypeID
WHERE (WasteSubType.WasteSubTypeID = 67)
)
ORDER BY Contractor.ContractorID
It produces a lovely unique list of ContractId's
ContractorId
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
I now need to do an insert based upon these results that I thought might look something like this:
INSERT INTO ContractorRecycle (ContractorId,WasteSubTypeId)
VALUES (
(SELECT DISTINCT Contractor.ContractorId
FROM Contractor
INNER JOIN ContractorContainer ON Contractor.ContractorID = ContractorContainer.ContractorId
WHERE ContractorContainer.ContainerId IN
(SELECT Container.ContainerID
FROM Container
INNER JOIN ContainerSizeResolve ON Container.ContainerID = ContainerSizeResolve.ContainerId
INNER JOIN WasteType ON ContainerSizeResolve.WasteTypeId = WasteType.WasteTypeId
INNER JOIN WasteSubTypeResolve ON WasteType.WasteTypeId = WasteSubTypeResolve.WasteTypeId
INNER JOIN WasteSubType ON WasteSubTypeResolve.WasteSubTypeId = WasteSubType.WasteSubTypeID
WHERE (WasteSubType.WasteSubTypeID = 67)
)
),
67)
Theat just results in a "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated." message.
What am I doing wrong here?
Regards
[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
2 Comments
Active today
INSERT INTO ContractorRecycle (ContractorId,WasteSubType
SELECT DISTINCT Contractor.ContractorId, 67
FROM Contractor
INNER JOIN ContractorContainer ON Contractor.ContractorID = ContractorContainer.Contra
WHERE ContractorContainer.Contai
(SELECT Container.ContainerID
FROM Container
INNER JOIN ContainerSizeResolve ON Container.ContainerID = ContainerSizeResolve.Conta
INNER JOIN WasteType ON ContainerSizeResolve.Waste
INNER JOIN WasteSubTypeResolve ON WasteType.WasteTypeId = WasteSubTypeResolve.WasteT
INNER JOIN WasteSubType ON WasteSubTypeResolve.WasteS
WHERE (WasteSubType.WasteSubType
)
SELECT DISTINCT Contractor.ContractorId, 67
FROM Contractor
INNER JOIN ContractorContainer ON Contractor.ContractorID = ContractorContainer.Contra
WHERE ContractorContainer.Contai
(SELECT Container.ContainerID
FROM Container
INNER JOIN ContainerSizeResolve ON Container.ContainerID = ContainerSizeResolve.Conta
INNER JOIN WasteType ON ContainerSizeResolve.Waste
INNER JOIN WasteSubTypeResolve ON WasteType.WasteTypeId = WasteSubTypeResolve.WasteT
INNER JOIN WasteSubType ON WasteSubTypeResolve.WasteS
WHERE (WasteSubType.WasteSubType
)
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Why is this different from all of the other step by step guides? Because I make a living as a DBA and not as a writer and I lived through this experience.
Defining the name:
When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month11 days, 13 hours left to enroll
623 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.