update and insert into table in with single query

table 1

id            pk1         timestamp
1              a          10-jul-2019
2              h          11-mar-2019
3              k           19-jul-2019
4              j           7-n0v-2018
5              h          11-jul-2019

table 2

col  start_date        end_date
a    10-jul-2019          
h    11-mar-2019       11-jul-2019
k    19-jul-2019          
j    7-nov-2018 
h    11-jul-2019

Q:> I want this process to be repeat after equal interval of time. if a new value got enter into the table 1 then same value should enter into the table 2 but if existing values enters in table 1 then just update end date of previous same value of table 2 and add one new value with null end date into table 2 (example value H in table 1 and table 2).

we need to use only single query.

with merge we are not able to get this

Answer

if a new value got enter into the table 1 then same value should enter into the table 2 but if existing values enters in table 1 then just update end date of previous same value of table 2 and add one new value with null end date into table 2

Your scenario need a Trigger to be created on Table1. You can put down your logic to update Table2 in the Trigger. See below demo:

--Table1 DDL
Create table tab1 (
  id           number,
  pk1          varchar(1),
  time_stamp   date
);

--Table2 DDL
create table tab2 (
  col          varchar(1),
  start_date   date,
  end_date     date
);

Here is Trigger on Table1

Create or replace trigger t1 
before insert on tab1
for each row
begin
  DECLARE
    l_exists   INTEGER;
  BEGIN
    SELECT COUNT(*) 
    INTO l_exists
    FROM tab2
    WHERE col = :new.pk1 ;    

    IF l_exists = 0
    THEN
      INSERT INTO TAB2
      values 
      (:new.pk1,:new.time_stamp,null);

    ELSE
      Update tab2
      set end_date = :new.time_stamp
      where col = :new.pk1;

     INSERT INTO TAB2
     values 
     (:new.pk1,:new.time_stamp,null);

    END IF;
  END;
end;

---

–Execution:

insert into tab1 values (1,'a',to_date('10-jul-2019','DD-MON-YYYY'));
insert into tab1 values (2,'h',to_date('11-mar-2019','DD-MON-YYYY'));
insert into tab1 values (3,'k',to_date('19-jul-2019','DD-MON-YYYY'));
insert into tab1 values (4,'j',to_date('07-nov-2019','DD-MON-YYYY'));
insert into tab1 values (5,'h',to_date('11-jul-2019','DD-MON-YYYY'));

Commit;

SQL> select * from tab1;

    ID P TIME_STAM
   ---------- - ---------
     1 a 10-JUL-19
     3 k 19-JUL-19
     4 j 07-NOV-19
     2 h 11-MAR-19
     5 h 11-JUL-19

 SQL> select * from tab2;

      C START_DAT END_DATE
      - --------- ---------
      a 10-JUL-19
      k 19-JUL-19
      j 07-NOV-19
      h 11-MAR-19 11-JUL-19
      h 11-JUL-19