Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.
Course Of The Month
8 days, 11 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 6 days ago
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…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Course of the Month8 days, 11 hours left to enroll
734 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.