Oracle 更新语句多表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15523520/
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 statement multiple tables
提问by needmorebeerformewallaby
I am using Oracle (Still a little new to it) and each time i run the update statement below i get the following error message.
我正在使用 Oracle（对它来说还是有点新），每次运行下面的更新语句时，我都会收到以下错误消息。
SQL Error: ORA-00904: "CH"."CONTRACT_ID": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Here is the query:
这是查询：
UPDATE wr00262_catalogue_201310 ct SET
ct.PORTFOLIO_ID = (SELECT ch.PORTFOLIO_ID
from WR00262_CONTRACT_HEADER ch
WHERE ch.PORTFOLIO_ID = ct.PORTFOLIO_ID)
WHERE ct.NPC in (SELECT ctl.NPC
FROM wr00262_contract_line ctl
WHERE ctl.CONTRACT_ID = ch.CONTRACT_ID);
I think i may need a join but not quite sure where or how. The contract_header table does have a column called CONTRACT_ID.
我想我可能需要加入，但不太确定在哪里或如何加入。contract_header 表确实有一个名为 CONTRACT_ID 的列。
回答by Egor Skriptunoff
This is a scoping issue. The columns in one sub-query are not visible to any other sub-query. So try something like this:
这是一个范围界定问题。一个子查询中的列对任何其他子查询都不可见。所以尝试这样的事情：
UPDATE wr00262_catalogue_201310 ct SET
PORTFOLIO_ID = (
SELECT ch.PORTFOLIO_ID
from WR00262_CONTRACT_HEADER ch
WHERE ch.PORTFOLIO_ID = ct.PORTFOLIO_ID
)
WHERE ct.NPC in (
SELECT ctl.NPC
FROM WR00262_CONTRACT_HEADER ch
join wr00262_contract_line ctl
on ctl.CONTRACT_ID = ch.CONTRACT_ID
WHERE ch.PORTFOLIO_ID = ct.PORTFOLIO_ID
);
