Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE dummy_test_table(seq_no) AS
SELECT '0000000957' FROM dual UNION ALL
SELECT '0000000957' FROM dual UNION ALL
SELECT '0000000958' FROM dual UNION ALL
SELECT '0000000958' FROM dual UNION ALL
SELECT '0000000969' FROM dual UNION ALL
SELECT '0000000969' FROM dual UNION ALL
SELECT '0000000957' FROM dual UNION ALL
SELECT '0000000958' FROM dual UNION ALL
SELECT '0000000959' FROM dual;
9 rows affected

ALTER TABLE dummy_test_table ADD batch_id VARCHAR2(3)
MERGE INTO dummy_test_table d1
USING (SELECT seq_no, LPAD(DENSE_RANK() OVER(ORDER BY seq_no), 3, '0') AS dr
         FROM dummy_test_table) d2
         ON (d1.rowid = d2.rowid)
 WHEN MATCHED THEN
   UPDATE SET d1.batch_id = dr
9 rows affected

SELECT * FROM dummy_test_table ORDER BY seq_no


SEQ_NO
BATCH_ID


0000000957
001

0000000957
001

0000000957
001

0000000958
002

0000000958
002

0000000958
002

0000000959
003

0000000969
004

0000000969
004

SEQ_NO	BATCH_ID
0000000957	001
0000000957	001
0000000957	001
0000000958	002
0000000958	002
0000000958	002
0000000959	003
0000000969	004
0000000969	004