与内部联接合并

Question

我正在尝试合并一个内部联接，以便可以使用3个不同的表，其中TBL1是将插入记录的目的地表，TBL2，其中要插入表1中的所有记录TBL1有效以及第三个也是最后一个表TBL3，其中条件将由rfc作出，其中，如果tbl3.rfc=tbl2.rfc，则将数据加载到TBL1。我正在进行的查询如下：

MERGE INTO   TBL1 concent
USING  (SELECT  inter.rfc,
arch.name_contr, arch.rfc,arch.taxpayer_situation, 
arch.oficio_global,arch.presumed_publication,arch.definitive_publication
FROM TBL2 arch
INNER JOIN  TBL3 inter 
ON inter.rfc = arch.rfc )
ON (concent.rfc = arch.rfc) WHEN MATCHED THEN UPDATE SET concent.name_contr = arch.name_contr, concent.taxpayer_situation = arch.taxpayer_situation, concent..oficio_global = arch.oficio_global, concent.presumed_publication = arch.presumed_publication, concent.definitive_publication = arch.definitive_publication, concent.id_arch = arch.id_arch WHEN NOT MATCHED THEN INSERT (concent.id_concent,concent.id_arch,conce.snapshot_date,concent.rfc,concent.name_contr,
concent.taxpayer_situation,concent.oficio_global,concent.presumed_publication,
concent.definitive_publication,concent.baja_logica,concent.last_update) VALUES (arch.id_arch, arch.id_arch,'04/05/2021',arch.rfc,arch.name_contr,
arch.taxpayer_situation,arch.oficio_global,arch.presumed_publication,
archi.definitive_publication,'01','05/05/2021');

它标记的错误是：命令行错误：8列：27错误报告-错误SQL:ORA-00904："；ARCH"RFC"：标识符00904无效。00000-"%s： 无效标识符"*原因：*行动：

数据库

Answer 1

表别名arch和inter的范围仅限于该子查询。如果要在父级merge上指定该子查询的列，则需要在using子句中为该子查询指定别名，例如v_using:

MERGE INTO   TBL1 concent
USING  (SELECT  inter.rfc as inter_rfc
arch.name_contr, arch.rfc,arch.taxpayer_situation, 
arch.oficio_global,arch.presumed_publication,arch.definitive_publication
FROM TBL2 arch
INNER JOIN  TBL3 inter 
ON inter.rfc = arch.rfc ) v_using
ON (concent.rfc = v_using.rfc) WHEN MATCHED THEN UPDATE SET concent.name_contr = v_using.name_contr, concent.taxpayer_situation = v_using.taxpayer_situation,...