How to add two conditions in merge statement using rowid and rownum

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 |
+--------+------+--------+---------+

Answer

If you’re wanting to group an odd and a consecutive even e_uuid together, you could always change the partition by clause to group on ceil(e_uuid/2), like so:

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

db<>fiddle

This works because 13/2 = 6.5, and the ceiling value of 6.5 = 7. 14/2 = 7, the ceiling value of which is also 7, since it’s already an integer. That makes 13 and 14 grouped together – same logic applies to 15 and 16 – the ceiling of them divided by two comes to 8 for both values.

So that explains the logic behind grouping them together (13 is odd, and 14 is the next consecutive even number).

The rows are then ordered by e_uuid, and then rowid to get the ordering you were after.

If you are only ever concerned about the 13, 14, 15, and 16 e_uuids, you might want to have a filter in your x subquery of where e_uuid in (13, 14, 15, 16), depending on if there are other values of e_uuid in your table.