Update column with the rank over parition
ysriJun 10 2010 — edited Jun 10 2010Hi, i have a table A with data as:
MASTER_ID, CHILD_ID
100, ..
100, ..
100, ..
101, ..
101, ..
102, ..
102, ..
102, ..
102, ..
The initial values of CHILD_ID are garbage and have to be updated as follows:
For each unique MASTER_ID the CHILD_ID should have values starting with 1 (with lowest rowid)
so when this column is updated the table should look as:
MASTER_ID, CHILD_ID
100, 1
100, 2
100, 3
101, 1
101, 2
102, 1
102, 2
102, 3
102, 4
select MASTER_ID, CHILD_ID,
rank() over(partition by MASTER_ID order by rowid) as rank
from A
gives proper ranking but when i try to update the CHILD_ID with this rank
update(
select MASTER_ID, CHILD_ID,
rank() over(partition by MASTER_ID order by rowid) as rank
from A)
set CHILD_ID=row_id
i get the error:
SQL Error: ORA-01732: data manipulation operation not legal on this view
01732. 00000 - "data manipulation operation not legal on this view"
Can you please help me with an update sql to set this child_id?
Thanks