How do you resolve ORA-01427 single row subquery returns more than one row?
Dmytro offered this advice to resolve ORA-01427: Try to add and rownum=1 to your subquery conditions if you DO NOT care about the value from the list or DO sure that they are the same. A single row subquery returns only one row. It can be used with the equal comparison operators (=,<,>,<>, etc).
Can we use multi row operation in single row subquery?
Multiple Row Subqueries Multiple row subquery returns one or more rows to the outer SQL statement. You may use the IN, ANY, or ALL operator in outer query to handle a subquery that returns multiple rows.
Which operator can be used in multiple row subqueries?
Operators that can be used with multiple-row subqueries include IN, ALL, ANY, and EXISTS. 10.
Which operator can be used with multiple row subqueries?
Multi-row operators that can be used with multiple-row subqueries include IN, ALL, ANY, and EXISTS.
Which comparison operator Cannot be used with multiple-row subqueries?
The outer query is then executed with the result from the inner query. A multiple-row subquery returns more than one row of data. The operators used in a single-row subqueries relational operators (=, <>, >, >=, <, <=) cannot be used in multiple-row subqueries.
Why does Ora 01427 return more than one row?
ORA-01427: single-row subquery returns more than one row This is because the subquery in the SELECT statement returned more than one row for the predicate department_id, which does not comply with a singular value limited operator, the equal =. Consequently, the statement fails to continue and then throw ORA-01427 to notify developers.
When does a subquery return more than one row?
If SQL engine expects your subquery to return a single row, it may throw ORA-01427 when the subquery returns more than one row unexpectedly. For example: This is because the subquery in the SELECT statement returned more than one row for the predicate department_id, which does not comply with a singular value limited operator, the equal =.
How to update row with multiple row in another table?
UPDATE TableA tabA SET ( tabA.trn_status ) = ( SELECT trn_status FROM TableB tabB WHERE ( tabA.trn_id = tabB.trn_id ) ) ; Limit your subquery with FETCH FIRST 1 ROW ONLY. If you can guarantee that all corresponding records are full duplicates, use DISTINCT.
How to resolve ora-01427 job failed by Trigger?
ORA-01427 in Job Failed by Trigger. I will talk about them respectively in the following sections. In which, the first error pattern is very common and easy to solve. But the second one is not so obvious, you need more patience to solve it.