Oracle:使用 ROWNUM 和 ORDER BY 子句更新表列

2022-01-17 00:00:00 sql-update sql oracle rownum

我想用一个正在运行的整数填充一个表列，所以我正在考虑使用 ROWNUM.但是，我需要根据其他列的顺序来填充它，例如 ORDER BY column1, column2.不幸的是，这是不可能的，因为 Oracle 不接受以下声明:

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:

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

也不是以下语句(尝试使用 WITH 子句):

Nor the following statement (an attempt to use WITH clause):

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

那么我该如何使用 SQL 语句而不使用 PL/SQL 中的游标迭代方法呢?

So how do I do it using an SQL statement and without resorting to cursor iteration method in PL/SQL?

推荐答案

这应该可以(对我有用)

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

或者您使用 MERGE 语句.像这样.

OR you use the MERGE statement. Something like this.

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

相关文章