SQL & PL/SQL

Main Table

CREATE TABLE NEW_DETAILS 
   (    E_ID NUMBER(10,0), 
    AC_EC VARCHAR2(50 BYTE), 
     CONSTRAINT PK_NEW_DETAILS PRIMARY KEY (E_ID));

INSERT INTO NEW_DETAILS VALUES(11,'AC')  ;

Reference Table:

CREATE TABLE lookup (
    ref_id      NUMBER(10),
    ref_typ     VARCHAR2(30),
    ref_typ_id  NUMBER(20),
    CONSTRAINT pk_lookup PRIMARY KEY ( ref_id )
);

insert into lookup values(1,'status',33);
insert into lookup values(2,'status',34);
insert into lookup values(3,'status',35);
insert into lookup values(4,'status',36);
insert into lookup values(5,'status',37);
insert into lookup values(6,'DUE_STATUS',29);
insert into lookup values(7,'DUE_STATUS',30);
insert into lookup values(8,'DUE_STATUS',31);
insert into lookup values(9,'DUE_STATUS',32);
insert into lookup values(10,'REM_STATUS',38);
insert into lookup values(11,'REM_STATUS',39);
insert into lookup values(12,'REM_STATUS',42);
insert into lookup values(13,'REM_STATUS',43);
insert into lookup values(14,'REM_STATUS',44);
insert into lookup values(15,'REM_STATUS',45);
insert into lookup values(16,'REM_STATUS',46);

create sequence limestone_sq;
CREATE TABLE limestone (
    limestone_id      NUMBER(10),
    e_id              NUMBER(10),
    ref_type_id       NUMBER(20),
    limestone_prg_id  NUMBER(10),
    sort_order        NUMBER(10),
    CONSTRAINT pk_limestone PRIMARY KEY ( limestone_id ),
    CONSTRAINT fk_pk_limestone_e_id FOREIGN KEY ( e_id )
        REFERENCES new_details ( e_id )
);


insert into limestone(
  limestone_id,
  e_id,
  ref_type_id,
  limestone_prg_id,
  sort_order
)
SELECT limestone_sq.nextval,
       e_id,
       ref_typ_id,
       case ref_typ_id
       when 33 then 3 
       when 34 then 2
       when 35 then 1 
       when 36 then 1 
       when 37 then 1
       end,
       ROW_NUMBER() OVER (PARTITION BY e_id ORDER BY ref_typ_id)
from   new_details CROSS JOIN lookup
where  ref_typ='status';


create sequence limestone_sub_sq;
CREATE TABLE limestone_sub (
    sub_id            NUMBER(10),
    limestone_id      NUMBER(10),
    sub_status_id     NUMBER(10),
    limestone_prg_id  NUMBER(10),
    CONSTRAINT pk_sub_id PRIMARY KEY ( sub_id ),
    CONSTRAINT fk_limestone_id FOREIGN KEY ( limestone_id )
        REFERENCES limestone ( limestone_id )
);

INSERT INTO limestone_sub (
    sub_id,
    limestone_id,
    sub_status_id,
    limestone_prg_id
)
    SELECT
        limestone_sub_sq.NEXTVAL,
        limestone_id,
        ref_typ_id AS sub_status_id,
        1
    FROM
             limestone
        CROSS JOIN lookup
    WHERE
            ref_typ = 'DUE_STATUS'
        AND ref_type_id = 34;

INSERT INTO limestone_sub (
    sub_id,
    limestone_id,
    sub_status_id,
    limestone_prg_id
)
    SELECT
        limestone_sub_sq.NEXTVAL,
        limestone_id,
        ref_typ_id AS sub_status_id,
        1
    FROM
             limestone
        CROSS JOIN lookup
    WHERE
            ref_typ = 'REM_STATUS'
        AND ref_type_id = 35;

I am inserting a total of 11 rows into the limestone_sub table based on the lookup table.
But I want to update the limestone_sub table SUB_STATUS_ID column that is equal to 1 to 2 and the remaining sub_status_id from 39 to 46 should remain 1 only. And if next time sub_status_id changes from 2 to 3 then need to update sub_status_id for 38 to 3 and 39 to 2 and from 40 to 46 remains 1. I have attached the screenshot for the expected output. I am confused about how this can be achieved.
My Attempt :

UPDATE limestone_sub set
    CASE
        WHEN limestone_prg_id = 1  THEN
            2
        WHEN limestone_prg_id = 2  THEN
            3
        ELSE
            3
    END
WHERE
    limestone_id IN (
        SELECT
            limestone_id
        FROM
            limestone
        WHERE
            e_id IN (
                SELECT
                    e_id
                FROM
                    new_details
            )
    )
    AND sub_status_id = 38