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.

Thanks

CodePudding user response：

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>

CodePudding user response：

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