How to update multiple rows within the same table in Oracle?

I am using Oracle DB Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

I have the following create table script. I want to update multiple rows within the same table with same values

For e.g.

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;

I want to update the table and set the batch id with same seq no with similar batch_id either through sql or plsql and get the o/p as below. Could someone pls help arrive at the query.

Seq_no	Batch_Id
0000000957	001
0000000957	001
0000000957	001
0000000958	002
0000000958	002
0000000958	002
0000000969	003
0000000969	003
0000000970	004

Thanks

Solution

With a little help of row_number analytic function:

SQL> ALTER TABLE dummy_test_table ADD batch_id VARCHAR2 (10);

Table altered.

SQL> UPDATE dummy_test_table a
  2     SET a.batch_id =
  3            (WITH
  4                temp
  5                AS
  6                   (SELECT seq_no,
  7                           ROW_NUMBER () OVER (ORDER BY seq_no) batch_id
  8                      FROM (SELECT DISTINCT seq_no
  9                              FROM dummy_test_table))
 10             SELECT LPAD (t.batch_id, 3, '0')
 11               FROM temp t
 12              WHERE t.seq_no = a.seq_no);

9 rows updated.

Result:

SQL>   SELECT *
  2      FROM dummy_test_table
  3  ORDER BY seq_no, batch_id;

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

9 rows selected.

SQL>