返回
"无效标识符"Partyid"(第2级)。
你能告诉我如何改正吗?
update table_x pe
set pe.taxidnumber =
(select tin
from (select tin
,version
,max(version) over(partition by partyid) maxversion
from table_y
where partyid = pe.partyid)
where version = maxversion);
在Oracle中,您可以在直接子查询(下一级)中使用表别名,但不能在更深嵌套的子查询中使用。
您可以使用分解子查询(with子句)来解决这个问题。但在您的情况下,不清楚为什么需要相关子查询。将partyid
匹配的WHERE条件移到中间查询;这样,最内部的查询将是不相关的,因此它将只计算一次。将partyid
复选框移到version = maxversion
旁边
我会这样做:
create global temporary table tempt as select tin, partyid from table_y where rownum < 2;
insert into tempt select tin, partyid
from (select tin
,partyid
,version
,max(version) over(partition by partyid) maxversion
from table_y
where version = maxversion);
merge into table_x t
using tempt d
on (t.partyid = d.partyid)
when matched then update set t.tin = d.tin;