Oracle：如何使用ROWNUM和ORDER BY子句更新表格列？

Question

我想用一个连续的整数来填充表格列，所以我考虑使用 ROWNUM。但是，我需要根据其他列的顺序来填充它，类似于 ORDER BY column1, column2。不幸的是，这是不可能的，因为 Oracle 不接受以下语句：

UPDATE table_a SET sequence_column = rownum ORDER BY column1, column2;

下面的语句（试图使用WITH子句）也不起作用：

WITH tmp AS (SELECT * FROM table_a ORDER BY column1, column2)
UPDATE tmp SET sequence_column = rownum;

那么，我该如何使用 SQL 语句来完成，而不需要使用 PL/SQL 中的游标迭代方法呢？

- Lukman

---

语句完成后，如果发生另一个 DML 操作，sequence_column 将会变得不正确/过时。为什么不将 sequence_column（和编号）放在视图中 - 这样将始终是正确的。 - Damien_The_Unbeliever

@Damien_The_Unbeliever 我不确定我是否理解你的意思，但是由于该列具有唯一索引，并且执行插入操作的脚本保证将下一个运行号放入该列中，因此未来向表中插入记录没有任何问题。只是在删除记录时，该列不再是连续的，需要重新排序。 - Lukman

@Lukman，我同意Damien的观点。最好在运行时计算ROWNUM或ROW_NUMBER()。 - Lukas Eder

@Lukman - 但如果你把这个编号放在一个视图中，你就永远不必进行这种维护活动了。 - Damien_The_Unbeliever

@Lukas 我有一个复杂的过程，其中包含验证脚本和审计日志，这些都严重依赖于序列编号，因此我希望将数字硬编码到列中，以便在整个过程中不会更改。我的协调员非常注重数据存储在表中，而不是动态生成数据，这是出于审计原因。 - Lukman

@Lukman，我明白了。那样说很有道理，不知怎么的。 - Lukas Eder

Answer 1

这应该可行（对我而言有效）。

update table_a outer 
set sequence_column = (
    select rnum from (

           -- evaluate row_number() for all rows ordered by your columns
           -- BEFORE updating those values into table_a
           select id, row_number() over (order by column1, column2) rnum  
           from table_a) inner 

    -- join on the primary key to be sure you'll only get one value
    -- for rnum
    where inner.id = outer.id);

或者您可以使用 MERGE 语句。类似这样：

merge into table_a u
using (
  select id, row_number() over (order by column1, column2) rnum 
  from table_a
) s
on (u.id = s.id)
when matched then update set u.sequence_column = s.rnum

- Lukas Eder

---

当我在SET子句中使用它时，Oracle会返回错误：ORA-30483：此处不允许使用窗口函数 >__< .. - Lukman

看我的更新。我已经尝试了一个更简单的表格。这会给你一个解决你情况的思路。 - Lukas Eder

我执行了这个语句，现在已经运行了将近5分钟。虽然表中只有大约15K条记录，但速度缓慢还是很奇怪的。 - Lukman

@Lukman，我会说这是预料之中的。如果你考虑到整个表需要排序，并且对每个记录进行row_number()评估...不过你也可以尝试使用“MERGE”语句。 - Lukas Eder

我完全喜欢MERGE语句，因为它是我最喜欢的SQL构造 :) .. 但我不知道如何在MERGE语句中强制排序。能否解释一下？ - Lukman

@Lukman，你说得对。ROWNUM不能用。应该使用ROW_NUMBER()窗口函数。另外：是啊，MERGE语句真是太有趣了！可以说是“全能”的语句。 - Lukas Eder

Answer 2

 UPDATE table_a
     SET sequence_column = (select rn 
                             from (
                                select rowid, 
                                      row_number() over (order by col1, col2)
                                from table_a
                            ) x
                            where x.rowid = table_a.rowid)

但这样并不会很快，正如Damien所指出的那样，每次更改表中的数据时都必须重新运行此语句。

- user330315

Answer 3

首先创建一个序列：

CREATE SEQUENCE SEQ_SLNO
  START WITH 1
  MAXVALUE 999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  NOCACHE
  NOORDER;

使用该序列更新表：

UPDATE table_name
SET colun_name = SEQ_SLNO.NEXTVAL;

- Palavesam

---

"ORDER BY" 部分怎么处理？ - Lukman

Answer 4

一小处更正，只需添加AS RN ：

UPDATE table_a
     SET sequence_column = (select rn 
                             from (
                                select rowid, 
                                      row_number() over (order by col1, col2) AS RN
                                from table_a
                            ) x
                            where x.rowid = table_a.rowid)

- Juan José López Aguilera