Optimum Upgrade Database prompted Subquery returned more than 1 value

Created by QNE Software Sdn bhd, Modified on Wed, 30 Jun, 2021 at 6:23 PM by QNE Software Sdn bhd

 

Scenario 

Upgrade Database prompted

“Subquery returned more than 1 value. 

This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.”

Solution 

  1. Connect your database with SSMS (SQL Server Management Studio)

https://support.qne.com.my/a/solutions/articles/81000385373 

  1. Right-click your server instance in Object Explorer, and then select New Query

  1. To find out which UserName has more than 1 row recorded for Subquery returned more than 1 value,

Paste the following T-SQL code snippet into the query window:

-----Replace [SampleDB_Testing] to your database name-----

USE [SampleDB_Testing]

GO

SELECT * FROM securitysystemuser WHERE username in 

(SELECT username FROM   (SELECT *, Row_number() OVER 

(partition BY username ORDER BY oid DESC) [Row] 

FROM    SecuritySystemUser) DUPLICATED

WHERE   [row] > 1 )

ORDER BY Username

  1. Execute the query by selecting Execute or selecting F5 on your keyboard.

  1. The results of the query are displayed under the area where the text was entered.

  1. To delete the duplicated rows only for Subquery returned more than 1 value,

Right-click your server instance in Object Explorer, and then select New Query

  1. Paste the following T-SQL code snippet into the query window:

-----Replace [SampleDB_Testing] to your database name-----

USE [SampleDB_Testing]

GO

DELETE FROM UserUsers_UserModelDifferenceObjectUserModelDifferenceObjects 

WHERE users in (SELECT Oid

FROM (SELECT *, Row_number() OVER (partition BY username ORDER BY oid DESC) [Row] 

FROM SecuritySystemUser) DUPLICATED 

WHERE [row] > 1);

GO

DELETE FROM SecuritySystemUserUsers_SecuritySystemRoleRoles 

WHERE users in (SELECT Oid

FROM (SELECT *, Row_number() OVER (partition BY username ORDER BY oid DESC) [Row] 

FROM SecuritySystemUser) DUPLICATED 

WHERE [row] > 1);

GO

DELETE DUPLICATED

FROM (SELECT *, Row_number() OVER (partition BY username ORDER BY oid DESC) [Row] 

FROM SecuritySystemUser) DUPLICATED 

WHERE [row] > 1;


  1. Execute the query by selecting Execute or selecting F5 on your keyboard.

  1. After the query is complete, duplicated rows is removed from your database
  2. Re-open Optimum application and proceed to upgrade database version

Know more about Subquery returned more than 1 value


Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article