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

Answer 2

It would be helpful to have a plain English explanation of what you are trying to accomplish. Having said that, it appears to me that you can accomplish what you want to do with the following SQL [assuming one to many relationship between school_table and student_table] having the inner select as a corelated sub-query with the outer update statement:

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

Hope this helps.

Regards, Roger

Answer 3

We all know exactly what the error says. SET only expects one value per column to be set. What we want to achieve is Update all rows for a given column using values from another table's column.

Now here's the solution:

BEGIN
For i in (select col_X, col_Y from table1) 
LOOP
Update table2 set col1 = i.col_X where col2 = i.col_Y;
END LOOP;
END;

That's how exactly you run it on SQLDeveloper worksheet. They say it's slow but that's the only solution that worked for me on this case.

Answer 4

your inner query..

select l.student_code
from school_table l, student_table n
where l.school = n.schoolname 

might return more than one value. Run the inner query and check the number of the value.

Answer 5

restrict the output of the inner query to one value to successfully run your query.

select l.student_code
from school_table l, student_table n
where l.school = n.schoolname 

check this

Answer 6

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.