Oracle 更新：ORA-01427：单行子查询返回多于一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17451643/
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
Oracle update: ORA-01427: single-row subquery returns more than one row
提问by user2501620
UPDATE TMP_COLUMNS
SET COLUMNNAME = (SELECT COLUMN_NAME FROM
user_tab_columns usertable
WHERE
table_name = table_Name
AND usertable.column_id = TMP_COLUMNS.idcolumn)
WHERE EXISTS (SELECT COLUMN_NAME
FROM
user_tab_columns usertable
WHERE
table_name = table_Name
AND usertable.column_id = TMP_COLUMNS.idcolumn);
COMMIT;
I am getting the error ORA-01427: single-row subquery returns more than one row
我收到错误 ORA-01427: single-row subquery returns more than one row
回答by mnagel
you do:
你做：
UPDATE TMP_COLUMNS SET COLUMNNAME = ( *SOMETHING* );
where SOMETHING is
某物在哪里
SELECT COLUMN_NAME FROM user_tab_columns usertable WHERE table_name = table_Name AND usertable.column_id = TMP_COLUMNS.idcolumn
that something returns more than one row, so your update is broken as it needs to know the new value to update to (and that needs to be exactly one value and not multiple rows).
某事返回不止一行，因此您的更新被破坏，因为它需要知道要更新到的新值（并且需要正好是一个值而不是多行）。
run the something part alone and fix it to return the proper value.
单独运行某些部分并修复它以返回正确的值。
also: dont write such long lines ;)
另外：不要写这么长的行；)
回答by Ed Gibbs
As @mnagel points out, the error is occurring because your subquery (SET COLUMNNAME = SELECT ...) is returning more than one row.
正如@mnagel 指出的那样，发生错误是因为您的子查询 ( SET COLUMNNAME = SELECT ...) 返回了不止一行。
The problem is probably here:
问题大概在这里：
WHERE table_name = table_Name
You need to do something like this instead:
你需要做这样的事情：
WHERE table_name = tmp_columns.something
Or if there isn't a table name column in tmp_columns, you need to supply the table name as a constant:
或者，如果 中没有表名列tmp_columns，则需要提供表名作为常量：
WHERE table_name = 'something'
