Solved

Error SubQuery Returns More than One Values

Posted on 2011-09-07
16
249 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 143

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

820 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