SQL ORA-01427: 单行子查询返回多于一行的更新...?? 帮助？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7509250/
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
ORA-01427: single-row subquery returns more than one row update...?? Help?
提问by sailaja
My Query returns this error ORA-01427: single-row subquery returns more than one row update, This is my query
我的查询返回此错误 ORA-01427：单行子查询返回多行更新，这是我的查询
Update Table_b B
Set B.Material_Desc = (Select A.Material_Desc From Table_a A Where A.PartNo = B.PartNo)
I have two different tables : Table_aand Table_b, both have same columns PartNoand Material_Desc. I want the Material_Descin Table_bto update the Material_Descin Table_awhen PartNoare equals.
我有两个不同的表：Table_a和Table_b，都有相同的列PartNo和Material_Desc。我希望Material_DescinTable_b更新Material_Descin Table_awhen PartNoare equals。
The above query returns the ORA-01427 error, Please can anyone correct my query ?
上面的查询返回 ORA-01427 错误，请问谁能更正我的查询？
回答by krtek
The problem is your subquery is returning a whole bunch of rows where you should have only one. You can't do this like this.
问题是您的子查询返回了一大堆您应该只有一个的行。你不能这样做。
Depending on the SQL database you're using, something like this should work better :
根据您使用的 SQL 数据库，这样的操作应该会更好：
UPDATE Table_b B
SET B.Materiel_Desc = A.Materiel_Desc
INNER JOIN Table_a A ON A.PartNo = B.PartNo
It is possible you must adapt the syntax to your database. For example, I think you cannot do it like this with MySQL. According to http://dev.mysql.com/doc/refman/5.0/en/update.htmlyou should do :
您可能必须使语法适应您的数据库。例如，我认为你不能用 MySQL 这样做。根据http://dev.mysql.com/doc/refman/5.0/en/update.html你应该这样做：
UPDATE Table_b, Table_A
SET Table_b.Materiel_Desc = Table_A.Materiel_Desc
WHERE Table_b.PartNo = Table_a.PartNo;
