SQL Oracle：使用 ROWNUM 和 ORDER BY 子句更新表列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6094039/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle: Updating a table column using ROWNUM in conjunction with ORDER BY clause
提问by Lukman
I want to populate a table column with a running integer number, so I'm thinking of using ROWNUM. However, I need to populate it based on the order of other columns, something like ORDER BY column1, column2. That is, unfortunately, not possible since Oracle does not accept the following statement:
我想用一个正在运行的整数填充一个表列，所以我在考虑使用 ROWNUM。但是，我需要根据其他列的顺序填充它，例如ORDER BY column1, column2. 不幸的是，这是不可能的，因为 Oracle 不接受以下语句：
UPDATE table_a SET sequence_column = rownum ORDER BY column1, column2;
Nor the following statement (an attempt to use WITH clause):
也不是以下语句（尝试使用 WITH 子句）：
WITH tmp AS (SELECT * FROM table_a ORDER BY column1, column2)
UPDATE tmp SET sequence_column = rownum;
So how do I do it using an SQL statement and without resorting to cursor iteration method in PL/SQL?
那么如何使用 SQL 语句而不使用 PL/SQL 中的游标迭代方法来实现呢？
回答by Lukas Eder
This should work (works for me)
这应该有效（对我有用）
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);
OR you use the MERGEstatement. Something like this.
或者您使用该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
回答by a_horse_with_no_name
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)
But that won't be very fast and as Damien pointed out, you have to re-run this statement each time you change data in that table.
但这不会很快，正如 Damien 指出的那样，每次更改该表中的数据时都必须重新运行此语句。
回答by Palavesam
First Create a sequence :
首先创建一个序列：
CREATE SEQUENCE SEQ_SLNO
START WITH 1
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER;
after that Update the table using the sequence:
之后使用序列更新表：
UPDATE table_name
SET colun_name = SEQ_SLNO.NEXTVAL;
回答by Juan José López Aguilera
A small correction just add AS RN:
一个小的修正只是添加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)
