Oracle: Updating a table column using ROWNUM in conjunction with ORDER BY clause

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

OR you use the MERGE statement. Something like this.

merge into table_a uusing (  select id, row_number() over (order by column1, column2) rnum   from table_a) son (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)

But that won't be very fast and as Damien pointed out, you have to re-run this statement each time you change data in that table.

First Create a sequence :

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

after that Update the table using the sequence:

UPDATE table_nameSET colun_name = SEQ_SLNO.NEXTVAL;