How to add a column in an already existing table and fill IDs in it ordered by a column on Oracle Server?

Question

In table TEST, I want to add a column ID and fill IDs in it incremented by 1 for each row. I need the IDs sorted with respect to column COL. So far, I have done this:

ALTER TABLE TEST ADD ID INTEGER;
UPDATE TEST SET ID = ROWNUM;

The problem in this method is IDs are not sorted with respect to COL. I need something like this:

UPDATE TEST SET ID = (SELECT ROWNUM FROM TEST ORDER BY COL);

but get an error. I have also tried CTE like this:

WITH CTE AS (
    SELECT * FROM TEST ORDER BY COL
) UPDATE CTE SET ROWNUM = ID;

Getting the error missing SELECT keyword.

Answer 1

update test
  set id = (
    with cte as (
      select row_number() over (order by col) id, rowid rid from test
    )
    select id from cte where test.rowid = cte.rid
  );

Answer 2

Usually such tasks are more easily solved with a merge statement. For example:

merge into test t
using (select rowid as rid, row_number() over (order by col) as rn from test) s
   on (s.rid = t.rowid)
when matched then update set id = rn;