Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

Error SubQuery Returns More than One Values

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
danielolorenz
Asked:
danielolorenz
  • 6
  • 5
  • 2
  • +3
1 Solution
 
edlunadCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
danielolorenzAuthor Commented:
Yes, the above is the complete query.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
Kevin CrossChief Technology OfficerCommented:
Hmm. You should not get that error message with that code; therefore, I would check for a trigger on tblInstance.
0
 
Forefront_Data_SolutionsCommented:
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
 
danielolorenzAuthor Commented:
Yes, I do get more than one row.
0
 
Forefront_Data_SolutionsCommented:
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
 
Ephraim WangoyaCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
danielolorenzAuthor Commented:
Yes, an update would work.  However, I got the same error on the update.
0
 
Kevin CrossChief Technology OfficerCommented:
How did you try update, I.e., please show code. Also did you check on trigger?
0
 
danielolorenzAuthor Commented:
I will have to test the code tomorrow at work.
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
danielolorenzAuthor Commented:
This worked

(817 row(s) affected)
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 6
  • 5
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now