How to check record exist before inserting in this query-CodePudding

I have following query which inserts (name & values) to the table and it works fine but I also need to check whether the data already exist or not in the table before inserting. If exist, no need of inserting again , just update or skip the loop but I am not able to make the logic in below query for this checking part. I appreciate your help on this. I am aware of Merge Into but I need to check multiple fields, in my case (name & values)

set serveroutput on
 DECLARE
  str VARCHAR2(100) := '1,2';
  BEGIN
    FOR i IN
    (SELECT trim(regexp_substr(str, '[^,] ', 1, LEVEL)) l
    FROM dual
      CONNECT BY LEVEL <= regexp_count(str, ',') 1
    )
    LOOP
    
     insert into TBL_TEST_CUSTOMER (NAME,VALUES) 
      SELECT
    regexp_substr('Name:check values,Name:bv,Name:cv', '(Name:)?(.*?)(,Name:|$)', 1, level, NULL,
                  2) AS "CatName",i.l
FROM
    dual
CONNECT BY
    level <= regexp_count('Name:check values,Name:bv,Name:cv', 'Name:');
    END LOOP;
  END;

CodePudding user response：

MERGE looks OK; another approach might be NOT EXISTS:

insert into TBL_TEST_CUSTOMER (NAME, VALUES) 
select "CatName", l
from (SELECT regexp_substr('Name:check values,Name:bv,Name:cv', '(Name:)?(.*?)(,Name:|$)', 1, level, NULL,2) AS "CatName",
             i.l 
      FROM dual
      CONNECT BY level <= regexp_count('Name:check values,Name:bv,Name:cv', 'Name:')
     ) x
where not exists (select null
                  from tbl_test_cusomer
                  where name = x.name
                    and values = x.l
                 );

CodePudding user response：

I think this solve my issues. please confirm my approach is correct. I appreciate any better way

set serveroutput on
 DECLARE
  str VARCHAR2(100) := '1,2';
  BEGIN
    FOR i IN
    (SELECT trim(regexp_substr(str, '[^,] ', 1, LEVEL)) l
    FROM dual
      CONNECT BY LEVEL <= regexp_count(str, ',') 1
    )
    LOOP
    
    MERGE INTO TBL_TEST_CUSTOMER TC USING
      (SELECT TMP1.CatName
      FROM
        (SELECT
    regexp_substr('Name:check values,Name:bv,Name:cv', '(Name:)?(.*?)(,Name:|$)', 1, level, NULL,
                  2) AS CatName
FROM
    dual
CONNECT BY
    level <= regexp_count('Name:check values,Name:bv,Name:cv', 'Name:')
        ) TMP1
      ) TMP ON (TC.NAME = TMP.CatName AND TC.VALUES= i.l )
    WHEN NOT MATCHED THEN
      INSERT
        ( NAME, VALUES
        ) VALUES
        ( TMP.CatName, i.l
        );
    
    END LOOP;
  END;