Insert only when values does not exist in oracle sql

Question

I have below query which i am using oracle stored procedure. The query is running fine but i want to add condition in this query such that if the combination of row values with name,e_id,c_date,p_check already exist then do not insert this values.

INSERT INTO M_LOG(NAME, E_ID, C_DATE, STATUS, P_CHECK)
Select MAX(ML.NAME), ML.E_ID, C_DATE, 1, 'M Step_1' from F_LOG ML,DIR_LOG MD
WHERE ML.NAME != MD.NAME and ML.E_ID != MD.E_ID and MD.C_DATE = LAST_DAY(to_date(sysdate,'YYYYMMDD'))
GROUP BY ML.E_ID,C_DATE;

Agree merge is more flexible, but you might also consider adding a not exists (select ...) to your existing insert. — William Robertson
– William Robertson, Commented Jan 17, 2018 at 15:56

MT0 · Accepted Answer · 2018-01-17 15:25:10Z

1

You can use MERGE with a WHEN NOT MATCHED clause:

MERGE INTO M_LOG dst
USING (
  Select MAX(ML.NAME) AS name,
         ML.E_ID,
         C_DATE,
         1 AS status,
         'M Step_1 As p_check'
  from   F_LOG ML
         INNER JOIN DIR_LOG MD
         ON (   ML.NAME != MD.NAME
            AND ML.E_ID != MD.E_ID )
  WHERE  MD.C_DATE = LAST_DAY( TRUNC( sysdate ) )
  GROUP BY ML.E_ID,C_DATE
) src
ON (   src.name    = dst.name
   AND src.e_id    = dst.e_id
   AND src.c_date  = dst.c_date
   AND src.p_check = dst.p_check )
WHEN NOT MATCHED THEN
  INSERT (NAME, E_ID, C_DATE, STATUS, P_CHECK)
  VALUES ( src.name, src.e_id, src.c_date, src.status, src.p_check );

answered Jan 17, 2018 at 15:25

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Marcus Over a year ago

sure i will try this

Kaushik Nayak · Accepted Answer · 2018-01-17 16:08:46Z

1

You could use NOT EXISTS.

 INSERT INTO m_log
            (
                name,
                e_id,
                c_date,
                status,
                p_check
            )
SELECT MAX(ml.name) name,
       ml.e_id,
       c_date,
       1          status,
       'M Step_1' p_check
FROM   f_log ml
WHERE  NOT EXISTS
       (
                SELECT   1
                FROM     dir_log md
                WHERE    ( ml.name = md.name
                               OR  ml.e_id = md.e_id )
                AND      md.c_date = LAST_DAY(TRUNC(SYSDATE)) )
                GROUP BY ml.e_id,
                         c_date;

edited Jan 17, 2018 at 16:08

answered Jan 17, 2018 at 16:02

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Collectives™ on Stack Overflow

Insert only when values does not exist in oracle sql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest