sql - first - Oracle: actualizando una columna de tabla utilizando ROWNUM junto con la cláusula ORDER BY

select rownum oracle example

Question

Quiero rellenar una columna de la tabla con un número entero en ejecución, por lo que estoy pensando en usar ROWNUM. Sin embargo, necesito rellenarlo según el orden de otras columnas, algo como ORDER BY column1, column2 . Desafortunadamente, esto no es posible ya que Oracle no acepta la siguiente declaración:

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

Tampoco la siguiente declaración (un intento de usar la cláusula WITH):

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

Entonces, ¿cómo lo hago usando una declaración SQL y sin recurrir al método de iteración del cursor en PL / SQL?

Answer 1

Esto debería funcionar (funciona para mí)

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

O utiliza la instrucción MERGE . Algo como esto.

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

Answer 2

Primero crea una secuencia:

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

después de eso actualiza la tabla usando la secuencia:

UPDATE table_name
SET colun_name = SEQ_SLNO.NEXTVAL;

Answer 3

Una pequeña corrección solo agrega 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)

Answer 4

 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)

Pero eso no será muy rápido y, como señaló Damien, debe volver a ejecutar esta declaración cada vez que cambie los datos en esa tabla.