Using a SELECT with Subquery as a source for an INSERT.
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
Asked:
Who is Participating?
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
-
Course of the Month11 days, 16 hours left to enrollMonitoring and Operating a Private Cloud with System Center 2012 R2 273 lessons
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
)