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