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

Question

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

score 2 · Accepted Answer · answered May 03 '22 at 09:26

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>

Barbaros Özhan · Answer 2 · 2022-05-03T10:07:32.807

One option is to use DENSE_RANK() analytic function within a MERGE DML statement such as

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

Demo

In my opinion, no need to add an extra column and populate it. Rather, you can use such a query or create a SQL-view(and query that whenever needed) :

--CREATE OR REPLACE v_dts AS
SELECT seq_no, LPAD(DENSE_RANK() OVER(ORDER BY seq_no), 3, '0') AS batch_id
  FROM dummy_test_table

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

2 Answers2