Stack Overflow
Stack Overflow

Questions tagged [ora-01427]

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

Definition:

ORA-01427 is an Oracle Database Error Message which stands for:

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

According to the Official Documentation:

Cause: The outer query must use one of the keywords ANY, ALL, IN, or NOT IN to specify values to compare because the subquery returned more than one row.

Action: Use ANY, ALL, IN, or NOT IN to specify which values to compare or reword the query so only one row is retrieved.

In other words, the comparison to the subquery is expecting to compare to a single value, but the subquery is returning more than one value.

This can be solved by changing the the equals operator to ANY, ALL, IN, or NOT IN.

Important Links:

31 questions
5
votes
6 answers

UpdateError: Receiving Error ORA - 01427 Single-row subquery returns more than one row

I am trying to update a column based on another column in the same table (student table) and a column from another table (school table) Code is: update student_table set student_code = (select l.student_code from school_table l, student_table…
user1307149
  • 1,427
  • 2
  • 16
  • 30
3
votes
3 answers

solving sub-query error ORA-01427 with join

I have a small sub-query in a lengthy select statement and I'm out of ideas (I get the same ORA-01427 error whether I'm working with the entire select statement or if I segregate out the sub-query. I have following: NAME table student_id_number…
SotaNice
  • 79
  • 4
3
votes
3 answers

Left Outer Join with subqueries?

---------- User ---------- user_ID(pk) UserEmail ---------- Project_Account ---------- actno actname projno projname ProjEmpID ProjEmpMGRID Where ProjEmpID,ProjEmpMGRID is the user_id and ProjEmpMGRID can be null. I need to look up the useremail…
jero
  • 543
  • 3
  • 13
  • 30
2
votes
1 answer

How can I unnest a collection of nested tables in a query?

I have a procedure that accepts an input similar to parent_arr shown below as input from the application layer via ODP.Net. In the first step of the procedure, I store data from the array in a global temporary table so I can proceed with several…
Thomas Gnade
  • 43
  • 1
  • 2
  • 4
2
votes
1 answer

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

I have query like this: select bb10.ID_SEKTOR_10 as kode, bb10.NAME_SEKTOR_10 as sektor10, ( select count(*) as HUBBNI_SEKTOR_EKONOMI10 from bbcust_debitur_dev where HUBBNI_SEKTOR_EKONOMI10 = bb10.ID_SEKTOR_10 and STATUS = 2 …
user3505775
  • 339
  • 2
  • 6
  • 20
2
votes
1 answer

ORA-01427 with table alias used in Procedure

I have the following snippet within a stored procedure: (Note, this is not the original snippet, but exactly similar to it except that table and column names have been replaced with more generic ones for better understanding as well as to prevent…
n00bcOd3r
  • 23
  • 4
1
vote
2 answers

How to match the longest string and update the value?

I need to compare and match the longest match of two strings in two different tables and update one values if there is a closest match. Table 1 Table 2 stack1 stack2 ABCDEFG ABC GHIJKLM ABCDE PQRSUVW ABCDEF I need to compare these…
sailaja
  • 379
  • 6
  • 15
  • 26
1
vote
2 answers

Update from another table Oracle

Table1 Tripid sequence Pattern 1 1 1 2 1 3 2 1 2 2 Table2 Tripid Pattern 1 A 2 B I'm trying to update Pattern for table 1, end result should be: Tripid sequence Pattern 1 1…
Sailormoon
  • 259
  • 1
  • 5
  • 23
1
vote
2 answers

ORA-01427 single-row subquery returns more that one row

I have run into a problem with Oracle SQL the Error is: ORA-01427 single-row subquery returns more that one row I am trying to find the locations that have at least one vehicle with manualtransmission that has lower mileage than any luxury vehicle…
Zabih Khaliqi
  • 85
  • 1
  • 8
1
vote
1 answer

Need to find an optimum method to update multiple rows in ORACLE 9 using one SQL

Issue desc: Need to match and update new desc(if different) production table from a flat file. Have imported flat file in temptable. sample from prod table: [mstr_plan] char(5) varchar2(30) char(3) PLAN_CODE PLAN_DESC FIN_CLUSTER BB123 …
SimpleKode2014
  • 13
  • 4
0
votes
2 answers

ORA-1427 in inline view

We have a query that fails in our Prod environment that fails with ora-01427 single-row subquery returns more rows. This is oracle 11g database. Query as below. This query runs fine till we add the final left outer join with SQ3, once added it fails…
Nocofoolme
  • 91
  • 8
0
votes
3 answers

Date query in Sql

I have a table where there are 5 columns let say a,b,c,d,tran_date. I want to generate a query to find out the minimum tran_date for every a,b,c,d in the table. Any help how this can be done. EDIT:The result of this query needs to be subtracted…
KLCoder
  • 89
  • 2
  • 3
  • 11
0
votes
1 answer

How to avoid duplicate data in the subquery

I have two tables as below. Product table: +-----+------------+-----+-------+--------+ | id | activityId | age | queue | status | +-----+------------+-----+-------+--------+ | 100 | 2 | 0 | start | 2 | | 101 | 3 | 0 | in …
saloni
  • 37
  • 1
  • 9
0
votes
1 answer

ORA-01427 Single-Row Subquery Returns More than One Row - how to resolve?

I've had this query running for over 2 years and just recently this began throwing the ORA-01427 single row sub-query returns multiple rows error. What's the best way to debug this when there are multiple sub-queries? Should I be adding a MAX…
elleoh
  • 1
  • 2
0
votes
1 answer

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

I have an update statement, at first it worked with some data, but after a while I've been having issues with some regiters , I got the following error: ORA-01427: single-row subquery returns more than one row . I thought it was the IN because the…
zepol
  • 187
  • 5
  • 20
1
2 3