CODEWITHSUNDEEP
databaseoracle-databasemergeinner-join
Salvador
Salvador

Reputation: 55

Merge with inner join

I am trying to merge an inner join so that I can use 3 different tables, where TBL1 is the destination table where the records will be inserted, TBL2 where all the records to insert in table 1 TBL1 live and the third and last table TBL3 where a condition will be made by rfc, where if tbl3.rfc = tbl2.rfc load the data to TBL1. My query that I am doing is the following:

   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');

The error it marks is: Command line error: 8 Column: 27 Error report - Error SQL: ORA-00904: "ARCH"."RFC": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause: *Action:

database

Upvotes: 0

Views: 763

Answers (1)

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8665

The scope of table aliases arch and inter is limited to that subquery only. If you want to specify columns from that subquery on the level of parent merge, you need to give alias to that subquery in using clause, for example 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,...

Upvotes: 1

Related Questions