如何使用另一个表中的另一列更新一列？SQL 错误：ORA-00933：SQL 命令未正确结束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9050743/
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
How to UPDATE one column using another column in another table? SQL Error: ORA-00933: SQL command not properly ended
提问by ZM Wang
I have tried everything I can think of but couldn't solve this SQL error:
我已经尝试了我能想到的所有方法，但无法解决此 SQL 错误：
SQL Error: ORA-00933: SQL command not properly ended
SQL 错误：ORA-00933：SQL 命令未正确结束
This is Oracle SQL.
这是 Oracle SQL。
UPDATE SALES_DATA_FAMILY_2007 A
SET A.POG_ID=B.POG_ID
FROM POG_HIERARCHY B
WHERE A.FAMILY_ID=B.FAMILY ;
One alternative I have tried is as below. Unfortunately it gives another error: ORA-01427: single-row subquery returns more than one row
我尝试过的一种替代方法如下。不幸的是，它给出了另一个错误：ORA-01427：单行子查询返回多于一行
UPDATE SALES_DATA_FAMILY_2007
SET POG_ID= (SELECT POG_HIERARCHY.POG_ID FROM POG_HIERARCHY
WHERE SALES_DATA_FAMILY_2007.FAMILY_ID=POG_HIERARCHY.FAMILY_ID)
WHERE EXISTS (SELECT POG_HIERARCHY.POG_ID FROM POG_HIERARCHY
WHERE SALES_DATA_FAMILY_2007.FAMILY_ID=POG_HIERARCHY.FAMILY_ID)
;
Please help!
请帮忙！
回答by A.B.Cade
If you want to update rows from a subquery with more than one record you can use the merge commenad:
如果要更新包含多个记录的子查询中的行，可以使用合并 commenad：
merge into SALES_DATA_FAMILY_2007 A
using (select POG_ID , FAMILY
FROM POG_HIERARCHY ) B
on (A.FAMILY_ID=B.FAMILY)
when matched then
update set A.POG_ID=B.POG_ID
回答by nolt2232
Try this:
尝试这个：
UPDATE SALES_DATA_FAMILY_2007 A
SET A.POG_ID =
(
SELECT P.POG_ID
FROM POG_HIERARCHY P
WHERE A.FAMILY_ID = P.FAMILY_ID
)
回答by Adam Musch
UPDATE SALES_DATA_FAMILY_2007 A
SET A.POG_ID= (select B.POG_ID
FROM POG_HIERARCHY B
WHERE A.FAMILY_ID=B.FAMILY) ;
This will work if and only if there's at most one corresponding row in POD_HIERARCHY for a given FAMILY. If there's more than one row in POD_HIERARCHY, then you need to determine which row from POD_HIERARCHY you wish to use the value from.
当且仅当给定 FAMILY 在 POD_HIERARCHY 中最多有一个对应的行时，这才有效。如果 POD_HIERARCHY 中有不止一行，那么您需要确定您希望使用 POD_HIERARCHY 中哪一行的值。
回答by BRADY
UPDATE SALES_DATA_FAMILY_2007 A
SET A.POG_ID= (select B.POG_ID
FROM POG_HIERARCHY B
WHERE A.FAMILY_ID=B.FAMILY)
WHERE A.POG_ID IN (SELECT C.POG_ID FROM POG_HIERARCHY C
WHERE A.FAMILY_ID = C.FAMILY);
