Sithara Jayachandran Sithara Jayachandran - 5 months ago 17
SQL Question

How to replace truncate- insert with merge clause in pl/sql

I need to update the records of id_number & entry_time.
There is no primary key in this table.
Can someone help me to convert this truncate-insert to merge statement:

CREATE OR REPLACE PACKAGE BODY PK_ENTRY_TIME IS
PROCEDURE PROC_ENTRY_TIME
AS
BEGIN
execute immediate 'truncate table TABLE_ENTRY_TIME';
insert into TABLE_ENTRY_TIME SELECT TABLE_CASE.ID_NUMBER , TABLE_ACT_ENTRY.ENTRY_TIME
FROM SA.TABLE_CASE TABLE_CASE INNER JOIN SA.TABLE_ACT_ENTRY TABLE_ACT_ENTRY
ON TABLE_CASE.OBJID = TABLE_ACT_ENTRY.ACT_ENTRY2CASE
commit;
END PROC_ENTRY_TIME;
END PK_ENTRY_TIME;

Answer

Since ID_number is a unique value,this worked:

 CREATE OR REPLACE PACKAGE BODY PK_ENTRY_TIME IS
    PROCEDURE PROC_ENTRY_TIME
    MERGE INTO TABLE_ENTRY_TIME A
    USING (SELECT  TABLE_CASE.ID_NUMBER , TABLE_ACT_ENTRY.ENTRY_TIME
    FROM  SA.TABLE_CASE TABLE_CASE INNER JOIN  SA.TABLE_ACT_ENTRY TABLE_ACT_ENTRY ON  TABLE_CASE.OBJID = TABLE_ACT_ENTRY.ACT_ENTRY2CASE)B

     ON (A.ID_NUMBER = B.ID_NUMBER)

      WHEN MATCHED THEN
      UPDATE SET
       A.ENTRY_TIME  = B.ENTRY_TIME 


    WHEN NOT MATCHED THEN   
    INSERT(ID_NUMBER,ENTRY_TIME) 
    VALUES(B.ID_NUMBER,B.ENTRY_TIME);  

COMMIT;
END  PROC_ENTRY_TIME;
END PK_ENTRY_TIME;