[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Using a SELECT with Subquery as a source for an INSERT.

Posted on 2013-05-10
2
Medium Priority
?
383 Views
Last Modified: 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:

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

Open in new window


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
...

Open in new window


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)

Open in new window


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
0
Comment
Question by:splanton
2 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 39156803
INSERT INTO ContractorRecycle (ContractorId,WasteSubTypeId)
SELECT DISTINCT Contractor.ContractorId, 67
      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)
            )
0
 
LVL 2

Author Closing Comment

by:splanton
ID: 39156948
Ah! I see... :)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
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

591 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