SQL UpdateError: 接收错误 ORA - 01427 单行子查询返回多于一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9970480/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
UpdateError: Receiving Error ORA - 01427 Single-row subquery returns more than one row
提问by user1307149
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
ORA - 01427 单行子查询返回多于一行
Any help would be appreciated.
任何帮助，将不胜感激。
回答by John Doyle
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:
如果您运行您的子查询，您会发现它返回不止一行。您正在尝试将一列更新为等于子查询的结果，因此它只需要一个值。您应该将子查询限制为仅返回一行，例如使用 max() 或 min() 或者，您可能打算加入外部 student_table？尝试：
update student_table n
set student_code =
(select l.student_code
from school_table l
where l.school = n.schoolname);
回答by Roger Cornejo
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:
对您要完成的工作有一个简单的英语解释会很有帮助。话虽如此，在我看来，您可以使用以下 SQL [假设 school_table 和 student_table 之间存在一对多关系] 将内部选择作为与外部更新语句相关的子查询来完成您想做的事情：
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
问候， 罗杰
回答by Pau Karr
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.
我们都知道错误的确切含义。SET 只需要为每列设置一个值。我们想要实现的是使用另一个表的列中的值更新给定列的所有行。
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.
这就是您在 SQLDeveloper 工作表上运行它的方式。他们说它很慢，但这是在这种情况下对我有用的唯一解决方案。
回答by chinna_82
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.
可能返回多个值。运行内部查询并检查值的编号。
回答by Balaswamy Vaddeman
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
回答by Ram
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.
如果您不关心列表中的值或确保它们相同，请尝试将和 rownum=1 添加到您的子查询条件中。
