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

sql oracle sql-update ora-01427
user1307149 picture user1307149 · Apr 2, 2012 · Viewed 35k times · Source

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 n
where l.school = n.schoolname)

I get the following error

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

Any help would be appreciated.

Answer

John Doyle picture John Doyle · Apr 2, 2012

If you run your subquery you'll find it returning more than one row. You are trying to update a column to be equal to the result of your subquery so it expects only one value. You should limit your subquery to only return one row such as using max() or min() or, perhaps you meant to join to the outer student_table? Try:

update student_table n
set student_code =
(select l.student_code
from school_table l
where l.school = n.schoolname);

Related questions

Oracle SQL: Update a table with data from another table

Table 1: id name desc ----------------------- 1 a abc 2 b def 3 c adf Table 2: id name desc ----------------------- 1 x 123 2 y 345 In oracle SQL, how do I run an sql update query that can update Table 1 with Table 2's name and desc using …

Read More
Update rows in one table with data from another table based on one column in each being equal

Update many rows into one table from another table based on one column in each being equal (user_id). both tables have a user_id column. Need to insert data from t2 into t1 when the user_id column are …

Read More
UPDATE with CASE and IN - Oracle

I wrote a query that works like a charm in SQL Server. Unfortunately it needs to be run on an Oracle db. I have been searching the web inside out for a solution on how to convert it, without any …

Read More