我想填充一个运行整数的表列，所以我正在考虑使用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;
解决方法
这应该工作(适用于我)
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,column2) rnum from table_a ) s on (u.id = s.id) when matched then update set u.sequence_column = s.rnum
