Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.
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
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
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes. We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses
Course of the Month6 days, 12 hours left to enroll
705 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.