CODEWITHSUNDEEP
sqloracle-database
w3learners
w3learners

Reputation: 11

The following query use for check duplicate data in table then update or insert row

I have the following query use for check duplicate data in table. If match data then update row else insert new row. In my case I have already one matched row in att_log table where emp_id=19.1.0121 and where mp_pk_id='32' AND att_date='2021-10-01', so result should be SET holiday=H in the matched row. But the DECLARE statement run without error and in console show affected row:1, but no change occur in data base, holiday not set to "H".

DECLARE c_emp_id att_log.emp_id%type;

BEGIN 
     SELECT emp_id
       INTO c_emp_id
       FROM att_log
      WHERE emp_id='19.1.0121'
        AND emp_pk_id='32'
        AND att_date='2021-10-01' ;

EXCEPTION
     WHEN TOO_MANY_ROWS THEN
          UPDATE att_log
             SET holiday =  'H',
                 updated_at = '2021-08-22'
           WHERE emp_id='19.1.0121'
             AND att_date='2021-10-01';

     WHEN NO_DATA_FOUND THEN
          INSERT INTO att_log (emp_id, emp_pk_id, att_date, holiday,login_time, logout_time)
                       VALUES ('19.1.0121', '32', '2021-10-01','H','','');
          COMMIT WORK;
     END;

If I run the query separately without DECLARE statement then data row change happen, but with the above DECLARE statement no change happen in data row in the ORACLE table. What is my fault! Sorry, I am new to ORACLE, and also sorry for poor English.

Upvotes: 0

Views: 55

Answers (1)

Jon Armstrong
Jon Armstrong

Reputation: 4694

A MERGE operation can INSERT or UPDATE (and also DELETE) depending on whether the row exists or not.

Here's a working test case:

Test case / fiddle

Example of MERGE:

CREATE TABLE logs (
     id        NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL
   , text      VARCHAR2(20)  UNIQUE
   , q         int DEFAULT 1
);

INSERT INTO logs (text) VALUES ('A');
INSERT INTO logs (text) VALUES ('B');
INSERT INTO logs (text) VALUES ('C');

MERGE INTO logs USING (SELECT 'B' AS text FROM dual) cte  ON (cte.text = logs.text)
 WHEN MATCHED THEN UPDATE SET logs.q = logs.q + 1
 WHEN NOT MATCHED THEN INSERT (logs.text)
     VALUES (cte.text)
;

Result:

enter image description here

and now we can do this for several existing rows and new rows at once:

MERGE INTO logs USING (
        SELECT text FROM logs WHERE text > 'A' UNION
        SELECT 'Z' FROM dual
     ) cte  ON (cte.text = logs.text)
 WHEN MATCHED THEN UPDATE SET logs.q = logs.q + 1
 WHEN NOT MATCHED THEN INSERT (logs.text)
     VALUES (cte.text)
;

New Result:

enter image description here

This example will either INSERT a new row when the rows in cte do not exist in logs, and UPDATE any existing rows in logs when matches are found, by incrementing q.

Upvotes: 1

Related Questions