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

Question

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 1

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);