Oracle: обновление столбца таблицы с использованием ROWNUM в сочетании с предложением ORDER BY

Я хочу заполнить столбец таблицы пробелом целочисленного числа, поэтому я думаю об использовании 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?

Ответ 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

Ответ 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)

Но это будет не очень быстро, и, как указал Дэмиен, вы должны повторно запускать это утверждение каждый раз, когда вы меняете данные в этой таблице.

Ответ 3

Сначала Создайте последовательность:

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

После этого обновите таблицу с помощью последовательности:

UPDATE table_name
SET colun_name = SEQ_SLNO.NEXTVAL;

Ответ 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)