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
- Connect your database with SSMS (SQL Server Management Studio)
https://support.qne.com.my/a/solutions/articles/81000385373
- Right-click your server instance in Object Explorer, and then select New Query
- 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
- Execute the query by selecting Execute or selecting F5 on your keyboard.
- The results of the query are displayed under the area where the text was entered.
- 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
- 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;
- Execute the query by selecting Execute or selecting F5 on your keyboard.
- After the query is complete, duplicated rows is removed from your database
- 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
Feedback sent
We appreciate your effort and will try to fix the article