UpdateError: Error getting ORA - 01427 Recharging with one line returns more than one line
I am trying to update a column based on another column in the same table (students table) and a column from another table (school table)
Code:
update student_table
set student_code =
(select l.student_code
from school_table l, student_table n
where l.school = n.schoolname)
I am getting the following error
ORA - 01427 Single line subquery returns more than one row
Any help would be appreciated.
source to share
It would be helpful to have a simple English explanation of what you are trying to accomplish. Having said that, it seems to me that you can accomplish what you want to do with the following SQL [assuming there is a relationship between many schools and student_table), in which the inner selection is a continuation subquery with an outer update statement:
update student_table
set student_code = (select l.student_code
from school_table
where school_table.school = student_table.schoolname)
;
Hope it helps.
Regards Roger
source to share
If you run your subquery, you find that it returns more than one row. You are trying to update a column equal to the result of your subquery so that it only expects one value. Should you restrict your subquery to only return one row, for example using max () or min (), or perhaps you wanted to join an external student table? Try:
update student_table n
set student_code =
(select l.student_code
from school_table l
where l.school = n.schoolname);
source to share
We all know exactly what the error says. SET only expects to be set to one value per column. We want to update all rows for a given column using values from another column in the table.
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;
Exactly how you run it in the SQLDeveloper worksheet. They say it slowly, but this is the only solution that worked for me in this case.
source to share