Oracle:如何使用ROWNUM和ORDER BY子句一起更新表列?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (28)

我想用一个正在运行的整数数字填充一个表列,所以我正在考虑使用ROWNUM。但是,我需要根据其他列的顺序来填充它,例如ORDER BY column1, column2。也就是说,不幸的是,由于oracle不接受以下声明,所以不可能:

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

也不是以下语句:

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

那么,如何使用SQL语句执行此操作,而无需在PL / SQL中使用游标迭代方法?

提问于
用户回答回答于

这个应该管用(对我有用)

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
用户回答回答于
 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)

扫码关注云+社区