How to check record exist before inserting in this query

Question

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;

Does this answer your question? [Oracle: how to UPSERT (update or insert into a table?)](https://stackoverflow.com/questions/237327/oracle-how-to-upsert-update-or-insert-into-a-table) — astentx, Jun 23 '22 at 17:51

score 1 · Answer 1 · answered Jun 23 '22 at 19:27

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

score 0 · Answer 2 · answered Jun 23 '22 at 18:38

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;

How to check record exist before inserting in this query

2 Answers2