Update an ordered list value with consecutive numbers

View All Public Scripts Login to Run Script

Script Name Update an ordered list value with consecutive numbers
Visibility Unlisted - anyone with the share link can access
Description I need to reset an ordered list to be consecutive numbers beginning with 1 and maintaining the original order.
Area SQL General / Data Manipulation
Contributor Don Simpson
Created Thursday April 11, 2019

Statement 1

create table T1 (ID_NUM int, PARENT_ID_NUM int, SIBLING_ORDER int)

Table created.

Statement 2

insert into T1  
    (ID_NUM, PARENT_ID_NUM, SIBLING_ORDER)  
select 1, null, null from dual union  
select 4, 1, 5498 from dual union  
select 3, 1, 73 from dual union  
select 2, 1, -6 from dual

4 row(s) inserted.

Statement 3
```
commit
```
```
Statement processed.
```

Statement 4

select * from T1


ID_NUM PARENT_ID_NUM SIBLING_ORDER
1  -  - 
2 1 -6
3 1 73
4 1 5498

4 rows selected.

ID_NUM	PARENT_ID_NUM	SIBLING_ORDER
1	-	-
2	1	-6
3	1	73
4	1	5498

Statement 5

update T1  
    set SIBLING_ORDER = rownum  
where PARENT_ID_NUM = 1

3 row(s) updated.

Statement 6

select * from T1


ID_NUM PARENT_ID_NUM SIBLING_ORDER
1  -  - 
2 1 1
3 1 2
4 1 3

4 rows selected.

ID_NUM	PARENT_ID_NUM	SIBLING_ORDER
1	-	-
2	1	1
3	1	2
4	1	3

Statement 7

update T1  
    set SIBLING_ORDER = 3 - SIBLING_ORDER  
where PARENT_ID_NUM = 1

3 row(s) updated.

Statement 8

select * from T1


ID_NUM PARENT_ID_NUM SIBLING_ORDER
1  -  - 
2 1 2
3 1 1
4 1 0

4 rows selected.

ID_NUM	PARENT_ID_NUM	SIBLING_ORDER
1	-	-
2	1	2
3	1	1
4	1	0

Statement 9

update T1  
    set SIBLING_ORDER = rownum  
where PARENT_ID_NUM = 1

3 row(s) updated.

Statement 10

select * from T1


ID_NUM PARENT_ID_NUM SIBLING_ORDER
1  -  - 
2 1 1
3 1 2
4 1 3

4 rows selected.

Statement 11
```
commit
```
```
Statement processed.
```
Statement 12
After I submitted my question and thought about it for a while, I came up with this.
Possible solution
```
update T1   
    set SIBLING_ORDER = 3 - SIBLING_ORDER   
where PARENT_ID_NUM = 1
```
```
3 row(s) updated.
```

ID_NUM	PARENT_ID_NUM	SIBLING_ORDER
1	-	-
2	1	1
3	1	2
4	1	3

Statement 13

After I submitted my question and thought about it for a while, I came up with this.

Possible solution

merge into T1 f  
using ( 
    with x (ID_NUM, SIBLING_ORDER) as ( 
        select ID_NUM,  
            row_number() over (partition by PARENT_ID_NUM order by SIBLING_ORDER, ID_NUM)  
        from T1  
            where PARENT_ID_NUM = 1  
        ) 
    select ID_NUM, SIBLING_ORDER from x  
    ) sib  
on ( 
    f.ID_NUM = sib.ID_NUM  
    ) 
when matched then  
    update   
        set f.SIBLING_ORDER = sib.SIBLING_ORDER  
    where f.ID_NUM = sib.ID_NUM

Statement processed.

Statement 14
After I submitted my question and thought about it for a while, I came up with this.
Possible solution
```
commit
```
```
Statement processed.
```
Statement 15
After I submitted my question and thought about it for a while, I came up with this.
Possible solution
```
select * from T1
```
```
ID_NUM PARENT_ID_NUM SIBLING_ORDER
1  -  - 
2 1 3
3 1 2
4 1 1

4 rows selected.
```

ID_NUM	PARENT_ID_NUM	SIBLING_ORDER
1	-	-
2	1	3
3	1	2
4	1	1

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database