SQL & PL/SQL

CREATE SEQUENCE e_demo2_tab_sq;

CREATE TABLE e_demo2_tab 
(
    tab_id   NUMBER(10) DEFAULT e_demo2_tab_sq.nextval NOT NULL,
    e_id     NUMBER(10),
    e_uuid   NUMBER(10),
    seq_cnt  NUMBER(10)
);

INSERT INTO e_demo2_tab VALUES(e_demo2_tab_sq.nextval, 11, 13, null);
INSERT INTO e_demo2_tab VALUES(e_demo2_tab_sq.nextval, 11, 13, null);
INSERT INTO e_demo2_tab VALUES(e_demo2_tab_sq.nextval, 11, 16, null);
INSERT INTO e_demo2_tab VALUES(e_demo2_tab_sq.nextval, 11, 15, null);
INSERT INTO e_demo2_tab VALUES(e_demo2_tab_sq.nextval, 11, 14, null);
INSERT INTO e_demo2_tab VALUES(e_demo2_tab_sq.nextval, 11, 14, null);
INSERT INTO e_demo2_tab VALUES(e_demo2_tab_sq.nextval, 11, 15, null);
INSERT INTO e_demo2_tab VALUES(e_demo2_tab_sq.nextval, 11, 16, null);

Query to load the sequence for e_uuid 13 & 15

merge into e_demo2_tab a
      using (select
               rowid rid,
               row_number() over (partition by e_id, e_uuid order by rowid) rn
             from e_demo2_tab where e_uuid in(13,15)
            ) x
    on (a.rowid = x.rid)
    when matched then update set a.seq_cnt = x.rn;

Then I want to merge into the same table for e_uuid 14 & 16 For 14: It should check e_uuid = 13 and maximum seq_cnt. Here(after executing my merge statement) maximum seq_cnt is 2 then the seq_cnt for 14 will come as 3 & 4. And if there are any null values then need to give by default 1 in seq_cnt
For 16: It should check e_uuid = 15 and maximum seq_cnt. Here(after executing my merge statement) maximum seq_cnt is 2 then the seq_cnt for 16 will come as 3 & 4.
Output after executing the merge statement given above

+--------+------+--------+---------+
| TAB_ID | E_ID | E_UUID | SEQ_CNT |
+--------+------+--------+---------+
|      1 | 11   |     13 | 1       |
|      2 | 11   |     13 | 2       |
|      3 | 11   |     16 | null    |
|      4 | 11`  |     15 | 1       |
|      5 | 11   |     14 | null    |
|      6 | 11   |     14 | null    |
|      7 | 11   |     15 | 2       |
|      8 | 11   |     16 | null    |
+--------+------+--------+---------+

Expected Output:

+--------+------+--------+---------+
| TAB_ID | E_ID | E_UUID | SEQ_CNT |
+--------+------+--------+---------+
|      1 | 11   |     13 |       1 |
|      2 | 11   |     13 |       2 |
|      3 | 11   |     16 |       3 |
|      4 | 11`  |     15 |       1 |
|      5 | 11   |     14 |       3 |
|      6 | 11   |     14 |       4 |
|      7 | 11   |     15 |       2 |
|      8 | 11   |     16 |       4 |
+--------+------+--------+---------+

Tool : SQL Developer
Version: 20.4