How will I be able to validate the column value from the table and merge into the another table-CodePudding

CREATE TABLE c_file (
    e_id   NUMBER(10),
    ac_ec  VARCHAR2(100),
    constraint pk_c_file primary key(e_id)
);

insert into c_file values(1,'A');
insert into c_file values(2,'A');
insert into c_file values(3,'E');
insert into c_file values(4,'A');
insert into c_file values(5,'E');

CREATE TABLE e_staging (
    e_id   NUMBER(10),
    ac_ec  VARCHAR2(100)
);

insert into e_staging values(1,null);
insert into e_staging values(2,null);
insert into e_staging values(3,null);
insert into e_staging values(4,null);
insert into e_staging values(5,null);
insert into e_staging values(6,null);
insert into e_staging values(7,null);
insert into e_staging values(8,null);
insert into e_staging values(9,null);
insert into e_staging values(10,null);

CREATE TABLE e_details (
    e_id   NUMBER(10),
    ac_ec  VARCHAR2(100)
);

Merge :

MERGE INTO e_details m
USING (
          SELECT
              e_id,
              ac_ec
          FROM
              e_staging
      )
s ON ( m.e_id = s.e_id )
WHEN MATCHED THEN UPDATE
SET m.ac_ec = s.ac_ec
WHEN NOT MATCHED THEN
INSERT (
    e_id,
    ac_ec )
VALUES
    ( s.e_id,
      s.ac_ec );

Issue :

By this Merge, it will load all the data from the e_staging table but while loading it should check the e_id into the c_file table if the e_id is present in c_file and in the e_staging table then it should update the ac_ec column based on the c_file ac_ec column if e_id is not present in e_staging then it should be updated to 'E'.

Current output :

Expected Output :

For e_id 1 - While loading into the e_details table from the e_staging table. It should check in the c_file table. If e_id is present then should update the ac_ec column to A. Likewise for all the e_id till 5.

From e_id 6 to 10. These are not present in c_file so value to be updated to 'E'.

Tool: SQL Developer Version: 20.4

CodePudding user response：

Join to c_file in the USING block of the MERGE statement.

MERGE INTO e_details m
USING (
        SELECT
            e.e_id,
            NVL(c.ac_ec,'E') AS ac_ec
        FROM
            e_staging e LEFT OUTER JOIN c_file c ON e.e_id = c.e_id  
      )
s ON ( m.e_id = s.e_id )
WHEN MATCHED THEN UPDATE
SET m.ac_ec = s.ac_ec
WHEN NOT MATCHED THEN
INSERT (
    e_id,
    ac_ec )
VALUES
    ( s.e_id,
      s.ac_ec );