Need not to insert duplicate records into the main table instead want to update the existing record-CodePudding

CREATE TABLE new_details_staging 
(
    e_id         NUMBER(10),
    e_name       VARCHAR2(30),
    portal_desc  VARCHAR2(50),
    risk_dec     VARCHAR2(50),
    CONSTRAINT pk_new_details_staging PRIMARY KEY (e_id)
);

INSERT INTO new_details_staging 
VALUES (11, 'A', 'AA', 'High');

INSERT INTO new_details_staging 
VALUES (22, 'B', 'BB', 'Low');

CREATE TABLE lookup_ref 
(
    ref_id       NUMBER(10),
    ref_typ      VARCHAR2(30),
    ref_typ_desc VARCHAR2(20),
    CONSTRAINT pk_lookup_ref PRIMARY KEY (ref_id)
);

INSERT INTO lookup_ref 
VALUES (181, 'portal', 'AA');

INSERT INTO lookup_ref 
VALUES (182, 'portal', 'BB');

INSERT INTO lookup_ref 
VALUES (183, 'risk', 'High');

INSERT INTO lookup_ref 
VALUES (184, 'risk', 'Low');

CREATE TABLE new_details_main 
(
    e_id    NUMBER(10),
    e_name  VARCHAR2(30),
    portal  NUMBER(20),
    risk    NUMBER(20),
    CONSTRAINT pk_new_details_main PRIMARY KEY (e_id)
);

COMMIT;

Store procedure --

I want to insert records into the main table from the staging through store procedure.

create or replace procedure sp_main(ov_err_msg OUT varchar2)
is
begin

INSERT INTO new_details_main (e_id, e_name, portal, risk)
SELECT n.e_id
,  n.e_name
,  (
   SELECT lp.ref_id
   FROM   lookup_ref lp -- lp is for Lookup-Portal
   WHERE  lp.ref_typ       = 'portal'
   AND    lp.ref_typ_desc  = n.portal_desc
  ),
  (
   SELECT lr.ref_id
   FROM   lookup_ref lr -- lr is for Lookup-Risk
   WHERE  lr.ref_typ       = 'risk'
   AND    lr.ref_typ_desc  = n.risk_dec
  )  
FROM  new_details_staging n
;
end;

This procedure is giving me the exact results which are required but if data is added into the staging table for the same e_id then it should update the record in the main table and not insert the new record. Example,

If I truncate the staging table and inserted a new row in which risk was Low but this time it got updated to High

INSERT INTO new_details_staging 
VALUES (22, 'B', 'BB', 'High');

So, in my main table, there should be 2 rows only but for e_id 22 it should have been updated to 183. How can I achieve this using store proc?

CodePudding user response：

I'd say you need MERGE.

Here's an example.

Procedure (I removed OUT parameter for simplicity; you didn't use it anyway). Also, I rewrote it so that it doesn't use subqueries for portal and risk values, but joined the same (lookup_ref) table twice (maybe you'll need to use outer join; data you have now suggest you don't, but - have that on mind):

SQL> CREATE OR REPLACE PROCEDURE sp_main
  2  IS
  3  BEGIN
  4     MERGE INTO new_details_main m
  5          USING (SELECT n.e_id,
  6                        n.e_name,
  7                        lp.ref_id portal,
  8                        lr.ref_id risk
  9                   FROM new_details_staging n
 10                        JOIN lookup_ref lp
 11                           ON     lp.ref_typ_desc = n.portal_desc
 12                              AND lp.ref_typ = 'portal'
 13                        JOIN lookup_ref lr
 14                           ON     lr.ref_typ_desc = n.risk_dec
 15                              AND lr.ref_typ = 'risk') x
 16             ON (m.e_id = x.e_id)
 17     WHEN MATCHED
 18     THEN
 19        UPDATE SET m.e_name = x.e_name, m.portal = x.portal, m.risk = x.risk
 20     WHEN NOT MATCHED
 21     THEN
 22        INSERT     (e_id,
 23                    e_name,
 24                    portal,
 25                    risk)
 26            VALUES (x.e_id,
 27                    x.e_name,
 28                    x.portal,
 29                    x.risk);
 30  END;
 31  /

Procedure created.

Testing: run the procedure with "initial" set of data (the one you posted):

SQL> EXEC sp_main;

PL/SQL procedure successfully completed.

Result is then:

SQL> SELECT * FROM new_details_main;

      E_ID E_NAME         PORTAL       RISK
---------- ---------- ---------- ----------
        11 A                 181        183
        22 B                 182        184

You said you'll then remove rows from the staging table and insert a new row:

SQL> DELETE FROM new_details_staging;

2 rows deleted.

SQL> INSERT INTO new_details_staging
  2  VALUES (22, 'B', 'BB', 'High');

1 row created.

Let's run the procedure again an check new result:

SQL> EXEC sp_main;

PL/SQL procedure successfully completed.

SQL> SELECT * FROM new_details_main;

      E_ID E_NAME         PORTAL       RISK
---------- ---------- ---------- ----------
        11 A                 181        183
        22 B                 182        183    --> RISK is modified to 183

SQL>