How to update sequence number to a existing table that contains data

February 3, 2022

CREATE TABLE e_demo_tab (
    e_id     NUMBER(10),
    e_uuid   NUMBER(10),
    seq_cnt  NUMBER(10)
);

INSERT INTO e_demo_tab VALUES(11,13,null);
INSERT INTO e_demo_tab VALUES(11,13,null);
INSERT INTO e_demo_tab VALUES(11,14,null);
INSERT INTO e_demo_tab VALUES(11,14,null);
INSERT INTO e_demo_tab VALUES(12,15,null);
INSERT INTO e_demo_tab VALUES(12,15,null);

I have one table e_demo_tab wherein I have to update the seq_cnt column based on the e_uuid column. If for the same e_id example for 11 we have e_uuid column as 13 which is appearing two times so the seq_cnt should be updated to 1 and 2 respectively and likewise for the same e_id i.e 11 for e_uuid 14 again it should update the count as 1 and 2 respectively. I am wondering if this can be handled using row_num or not. Please suggest.

Expected Output:

+------+--------+---------+--+
| E_ID | E_UUID | SEQ_CNT |  |
+------+--------+---------+--+
|   11 |     13 |       1 |  |
|   11 |     13 |       2 |  |
|   11 |     14 |       1 |  |
|   11 |     14 |       2 |  |
|   12 |     15 |       1 |  |
|   12 |     15 |       2 |  |
+------+--------+---------+--+

>Solution :

One option is to use ROW_NUMBER analytic function (to "create" the sequence number), and then MERGE on ROWID (as there aren’t any columns which would uniquely represent a single row).

SQL> merge into e_demo_tab a
  2    using (select
  3             rowid rid,
  4             row_number() over (partition by e_id, e_uuid order by rowid) rn
  5           from e_demo_tab
  6          ) x
  7  on (a.rowid = x.rid)
  8  when matched then update set a.seq_cnt = x.rn;

6 rows merged.

SQL> select * from e_demo_tab order by e_id, e_uuid, seq_cnt;

      E_ID     E_UUID    SEQ_CNT
---------- ---------- ----------
        11         13          1
        11         13          2
        11         14          1
        11         14          2
        12         15          1
        12         15          2

6 rows selected.

SQL>