Solved

Error SubQuery Returns More than One Values

Posted on 2011-09-07
16
248 Views
Last Modified: 2012-05-12
When executing a query I am getting the following error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

How do you fix this error?

ALTER PROCEDURE [dbo].[sp_UpdateInstanceTableWithOperatioGroup]
AS
BEGIN
INSERT INTO tblInstance (SupportGroup)
SELECT OperationSupportGroup from tblDatabase join tblInstance on
                                    tblInstance.Instance_id = tblDatabase.Instance_id where tblInstance.DBPlatform = 'MSSQL'
END

Thanks,

Dan
0
Comment
Question by:danielolorenz
  • 6
  • 5
  • 2
  • +3
16 Comments
 
LVL 9

Expert Comment

by:edlunad
ID: 36499486
Do you need to insert more than one record? if not just try to select the first row from the query like this...

ALTER PROCEDURE [dbo].[sp_UpdateInstanceTableWithOperatioGroup]
AS
BEGIN
INSERT INTO tblInstance (SupportGroup)
SELECT Top 1 OperationSupportGroup from tblDatabase join tblInstance on
                                    tblInstance.Instance_id = tblDatabase.Instance_id where tblInstance.DBPlatform = 'MSSQL'
END
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36499548
Usually you get that error message where you have a correlated sub-query in the SELECT or in WHERE clause with comparison operator like =, >=, etc. that returns more than one row. Is the above the complete query or was it simplified for here?
0
 

Author Comment

by:danielolorenz
ID: 36499601
Yes, the above is the complete query.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36499613
Hmm. You should not get that error message with that code; therefore, I would check for a trigger on tblInstance.
0
 
LVL 8

Expert Comment

by:Forefront_Data_Solutions
ID: 36499708
If you execute the following query (which is the subquery in your SP), do you get more than 1 row?

SELECT OperationSupportGroup from tblDatabase join tblInstance on
                                    tblInstance.Instance_id = tblDatabase.Instance_id where tblInstance.DBPlatform = 'MSSQL'


If so, then either use edlunad's suggestion above to select TOP 1, or figure out if there another field or value that you can narrow the query down to.  If you post your table structures with all keys, someone can probably help.
0
 

Author Comment

by:danielolorenz
ID: 36499713
Yes, I do get more than one row.
0
 
LVL 8

Expert Comment

by:Forefront_Data_Solutions
ID: 36499724
Ok....if you are trying to insert multiple rows, change your SP to this:

ALTER PROCEDURE [dbo].[sp_UpdateInstanceTableWithOperatioGroup]
AS
BEGIN
INSERT INTO tblInstance
SELECT OperationSupportGroup from tblDatabase join tblInstance on
                                    tblInstance.Instance_id = tblDatabase.Instance_id where tblInstance.DBPlatform = 'MSSQL'

All I did was remove "(SupportGroup)" after tblInstance.  That should work.  However, I don't know if that is satisfying the business case for this query or not.
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 36499787
TRY
ALTER PROCEDURE [dbo].[sp_UpdateInstanceTableWithOperatioGroup]
AS
BEGIN
	;with cte as
	(
		SELECT DISTINCT OperationSupportGroup 
		from tblDatabase 
		inner join tblInstance on tblInstance.Instance_id = tblDatabase.Instance_id 
		where tblInstance.DBPlatform = 'MSSQL'
	)
	
	INSERT INTO tblInstance (SupportGroup)
	SELECT OperationSupportGroup
	FROM cte
END

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36499805
The last statement may work if there is an issue with a trigger that is depending on SupportGroup being unique, but for some reason you have duplicate values within your SELECT statement.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36499816
If that is the case, then it is very odd for you to be inserting into the same table which you are joining on. That probably is guaranteeing in some fashion that you are going to insert a duplicate value, right?

Do you possibly want to UPDATE the row of the tblInstance with a value from tblDatabase where the instance_id values match and the DBPlatform = 'MSSQL'?
0
 

Author Comment

by:danielolorenz
ID: 36500329
Yes, an update would work.  However, I got the same error on the update.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36500336
How did you try update, I.e., please show code. Also did you check on trigger?
0
 

Author Comment

by:danielolorenz
ID: 36500339
I will have to test the code tomorrow at work.
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36500385
If your code was not similar to this, then give this a try:
ALTER PROCEDURE [dbo].[sp_UpdateInstanceTableWithOperatioGroup]
AS
BEGIN
UPDATE tto
SET tto.SupportGroup = tfr.OperationSupportGroup
FROM tblInstance tto
JOIN tblDatabase tfr ON tfr.Instance_id = tto.Instance_id
AND tto.DBPlatform = 'MSSQL';
END

Open in new window


If you get the same error message, then you multiple rows in tblDatabase matching tblInstance. If that is the case, how do you want to select the value that goes into SupportGroup, i.e., first, last, min, max, comma-delimited list of all, etc.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36502372
I confirm that the error message does NOT come from the insert/update itself, but from a trigger on the inserted table.
please post that code, if you cannot fix that yourself.
0
 

Author Closing Comment

by:danielolorenz
ID: 36502865
This worked

(817 row(s) affected)
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

776 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