Hi all,
I have a table where one of the fields is mostly empty.
with myTable (id, name, sort) as (
select 1, 'B', null from dual union all
select 3, 'A', null from dual union all
select 17, 'C', null from dual union all
select 2, 'D', null from dual union all
select 5, 'E', null from dual union all
)
select myTable.*
In my real table, the corresponding SELECT statement is:
select id, name, sort
from <someTable>
where <some other field = some fixed value>
Now, for that part of the data in the table, I want to populate the column sort with an ascending number.
I've managed to display that number as follows:
with myTable (id, name, sort) as (
select 1, 'B', null from dual union all
select 3, 'A', null from dual union all
select 17, 'C', null from dual union all
select 2, 'D', null from dual union all
select 5, 'E', null from dual union all
)
select myTable.*, row_number() over (order by name) rn from myTable
But I'm not getting a decent update or merge statment together. For instance, I've tried:
update (
select id, name, sort, row_number() over (order by name) as rn
from <someTable>
where <some other field = some fixed value>
)
set sort = rn
>> ORA-01732: data maniulation oeration not legal on this view
I feel like this should be a simple oeration (which it probably is once you know how to do it ...). Any help welcome!