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.