sql - Update existing records based on the order from a different column -

i have following table:

x_id  x_name  x_type  x_sort_id  10    book     1       null    20    pen      1       null  30    watch    2       null   5    tent     3       null 

what i'm trying achieve populate x_sort_id column incremented values starting 1 based on value in x_id. table this:

x_id  x_name  x_type  x_sort_id  10    book     1       2    20    pen      1       3  30    watch    2       4   5    tent     3       1 

i need update table existing rows. records added in future use sequence set x_sort_id field next value.

the query came not need.

update x_items  set x_sort_id = (select max(x_id) x_items) + rownum x_sort_id null; 

i use rownum, assign value of 4 last record x_id = 5, not wanted.

i'd thankful suggestions.

can use oracle row_number :

update query

update items ot set x_sort_id =  (    select rw    (      select x_id, row_number() on ( order x_id ) rw items   )   it.x_id = ot.x_id  ) ; 

result table

+------+--------+--------+-----------+ | x_id | x_name | x_type | x_sort_id | +------+--------+--------+-----------+ |   10 | book   |      1 |         2 | |   20 | pen    |      1 |         3 | |   30 | watch  |      2 |         4 | |    5 | tent   |      3 |         1 | +------+--------+--------+-----------+ 

sqlfiddle