Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Update column with the rank over parition

ysriJun 10 2010 — edited Jun 10 2010
Hi, 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
This post has been answered by Tubby on Jun 10 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 8 2010
Added on Jun 10 2010
3 comments
5,918 views