ORA-01427: single-row subquery returns more than one row

The working Updates' structure is the same as the erroneous one.

This works:

UPDATE P0525_STOREROOM_HOLDER H SET H.STRATIFICATION_ID = (SELECT X.STRATIFICATION_ID FROM EMISTRATIFICATION_XS X WHERE H.TOA = X.TOA AND H.STOREROOM = X.STOREROOM AND H.NSN = X.NSN AND X.ASSEMBLY = 'NO REQUIREMENT' );

This one gives me a single-row error:

UPDATE P0525_STOREROOM_HOLDER H SET H.STRATIFICATION_ID = (SELECT X.STRATIFICATION_ID FROM EMISTRATIFICATION_XS X WHERE H.TOA = X.TOA AND H.STOREROOM = X.STOREROOM AND H.NSN = X.NSN AND X.ASSEMBLY = 'ABOVE ALLOWANCE' AND H.NSN_QUANTITY > 0);

Can someone explain why this might be happening? I have run a check on the data and there doesn't appear to be any duplicate values in the second update... Both Updates are supposed to be updating record sets not a single row (i.e. the stratification_id where the criteria matches... so I don't understand why one would work and the other would not.

Any insight is greatly appreciated,

-Gary]]>

WHERE H.TOA = X.TOA

AND H.STOREROOM = X.STOREROOM

AND H.NSN = X.NSN

AND X.ASSEMBLY = 'ABOVE ALLOWANCE'

AND H.NSN_QUANTITY > 0;

what does SQL produce?

Any value greater than will throw the error.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

]]>

SELECT COUNT(*) FROM EMISTRATIFICATION_XS X WHERE H.TOA = X.TOA AND H.STOREROOM = X.STOREROOM AND H.NSN = X.NSN AND X.ASSEMBLY = 'ABOVE ALLOWANCE' AND H.NSN_QUANTITY > 0;

Any value greater than...?]]>

Anyway, expected result is larger than 1. What you should do is to add additional condition(s) to the WHERE clause which will further restrict result set and make sure that at most 1 record is returned. Otherwise, Oracle just doesn't know which value of 2, 3, ... , many X.STRATIFICATION_ID to put into H.STRATIFICATION_ID. You know, the principle: there can be only one sitting there at the moment.]]>

Thanks so much!]]>