如何在Oracle中更新同一表中的多行?

Question

我使用的是Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

我有下面的创建表脚本。我想用相同的值更新同一表中的多行

例如

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;

我想更新表并通过sql或plsql设置具有相同seq no的batch id，并获得如下所示的o/p。有没有人可以帮助到达查询。

<表类>Seq_noBatch_Idtbody><<tr>000000095700100000009570010000000957001000000095800200000009580020000000958002000000096900300000009690030000000970004

借助row_number解析函数:

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.

结果:

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>

一种选择是在MERGE DML语句中使用DENSE_RANK()分析函数，例如

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

在我看来，不需要添加额外的列并填充它。相反，您可以使用这样的查询或创建sql视图(和查询，无论何时需要):

--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