Solved

Error SubQuery Returns More than One Values

Posted on 2011-09-07
16
247 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Copy Database Wizard 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.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…

929 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now