Solved

Error SubQuery Returns More than One Values

Posted on 2011-09-07
16
250 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 60

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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 60

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:Ephraim Wangoya
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 60

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 60

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 60

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 60

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Amazon RDS migrate to SQL Server 3 50
SQL trigger 5 51
SQL Query Across Multiple Tables - Help 5 48
SQL Server how to create a DYNAMIC TABLE? 11 51
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
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.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

751 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