오랜만에 다시 SQL을 하려고 해보니 이거 영 힘드네요

한 3년만에 다시 돌아와서 하려고 하는데 잘 안되서 문의드립니다.

select '1001' as TMP_A, 'A1234' TMP_B, '1' TMP_C, '0' TMP_D, 'D' TMP_E from dual union all
select '1001' as TMP_A, 'A1234' TMP_B, '3' TMP_C, '1' TMP_D, 'A' TMP_E from dual union all
select '1001' as TMP_A, 'A1234' TMP_B, '4' TMP_C, '1' TMP_D, 'D' TMP_E from dual union all
select '1001' as TMP_A, 'A1234' TMP_B, '6' TMP_C, '2' TMP_D, 'A' TMP_E from dual union all
select '1001' as TMP_A, 'A1234' TMP_B, '7' TMP_C, '2' TMP_D, 'D' TMP_E from dual union all
select '1001' as TMP_A, 'A1234' TMP_B, '8' TMP_C, '0' TMP_D, 'D' TMP_E from dual union all
select '1001' as TMP_A, 'A1234' TMP_B, '9' TMP_C, '1' TMP_D, 'D' TMP_E from dual union all
select '1001' as TMP_A, 'A1234' TMP_B, '10' TMP_C, '2' TMP_D, 'A' TMP_E from dual union all
select '1001' as TMP_A, 'A1234' TMP_B, '11' TMP_C, '2' TMP_D, 'D' TMP_E from dual union all
select '1001' as TMP_A, 'A1234' TMP_B, '12' TMP_C, '3' TMP_D, 'A' TMP_E from dual union all
select '1002' as TMP_A, 'A1234' TMP_B, '13' TMP_C, '0' TMP_D, 'D' TMP_E from dual union all
select '1002' as TMP_A, 'A1234' TMP_B, '15' TMP_C, '1' TMP_D, 'A' TMP_E from dual union all
select '1002' as TMP_A, 'A1234' TMP_B, '16' TMP_C, '1' TMP_D, 'D' TMP_E from dual union all
select '1002' as TMP_A, 'A1234' TMP_B, '18' TMP_C, '2' TMP_D, 'D' TMP_E from dual union all
select '1003' as TMP_A, 'A1235' TMP_B, '1' TMP_C, '0' TMP_D, 'D' TMP_E from dual union all
select '1003' as TMP_A, 'A1235' TMP_B, '3' TMP_C, '1' TMP_D, 'A' TMP_E from dual union all
select '1003' as TMP_A, 'A1235' TMP_B, '10' TMP_C, '1' TMP_D, 'D' TMP_E from dual union all
select '1003' as TMP_A, 'A1235' TMP_B, '11' TMP_C, '2' TMP_D, 'D' TMP_E from dual union all
select '1004' as TMP_A, 'A1235' TMP_B, '72' TMP_C, '0' TMP_D, 'D' TMP_E from dual union all
select '1004' as TMP_A, 'A1235' TMP_B, '78' TMP_C, '1' TMP_D, 'A' TMP_E from dual union all
select '1004' as TMP_A, 'A1235' TMP_B, '79' TMP_C, '1' TMP_D, 'D' TMP_E from dual union all
select '1004' as TMP_A, 'A1235' TMP_B, '80' TMP_C, '2' TMP_D, 'D' TMP_E from dual

위의 Data에 아래와 같이 Tmp_e를 변경 하고자 합니다

요지는 Tmp_A의 같은 아이디중 tmp_c의 순번이 변경될때 까지의 rownum을 동일하게 넣고싶습니다.

select '1001' as TMP_A, 'A1234' TMP_B, '1' TMP_C, '0' TMP_D, '1' TMP_E from dual union all
select '1001' as TMP_A, 'A1234' TMP_B, '3' TMP_C, '1' TMP_D, '1' TMP_E from dual union all
select '1001' as TMP_A, 'A1234' TMP_B, '4' TMP_C, '1' TMP_D, '1' TMP_E from dual union all
select '1001' as TMP_A, 'A1234' TMP_B, '6' TMP_C, '2' TMP_D, '1' TMP_E from dual union all
select '1001' as TMP_A, 'A1234' TMP_B, '7' TMP_C, '2' TMP_D, '1' TMP_E from dual union all
select '1001' as TMP_A, 'A1234' TMP_B, '8' TMP_C, '0' TMP_D, '2' TMP_E from dual union all
select '1001' as TMP_A, 'A1234' TMP_B, '9' TMP_C, '1' TMP_D, '2' TMP_E from dual union all
select '1001' as TMP_A, 'A1234' TMP_B, '10' TMP_C, '2' TMP_D, '2' TMP_E from dual union all
select '1001' as TMP_A, 'A1234' TMP_B, '11' TMP_C, '2' TMP_D, '2' TMP_E from dual union all
select '1001' as TMP_A, 'A1234' TMP_B, '12' TMP_C, '3' TMP_D, '2' TMP_E from dual union all
select '1002' as TMP_A, 'A1234' TMP_B, '13' TMP_C, '0' TMP_D, '1' TMP_E from dual union all
select '1002' as TMP_A, 'A1234' TMP_B, '15' TMP_C, '1' TMP_D, '1' TMP_E from dual union all
select '1002' as TMP_A, 'A1234' TMP_B, '16' TMP_C, '1' TMP_D, '1' TMP_E from dual union all
select '1002' as TMP_A, 'A1234' TMP_B, '18' TMP_C, '2' TMP_D, '1' TMP_E from dual union all
select '1003' as TMP_A, 'A1235' TMP_B, '1' TMP_C, '0' TMP_D, '1' TMP_E from dual union all
select '1003' as TMP_A, 'A1235' TMP_B, '3' TMP_C, '1' TMP_D, '1' TMP_E from dual union all
select '1003' as TMP_A, 'A1235' TMP_B, '10' TMP_C, '1' TMP_D, '1' TMP_E from dual union all
select '1003' as TMP_A, 'A1235' TMP_B, '11' TMP_C, '2' TMP_D, '1' TMP_E from dual union all
select '1004' as TMP_A, 'A1235' TMP_B, '72' TMP_C, '0' TMP_D, '2' TMP_E from dual union all
select '1004' as TMP_A, 'A1235' TMP_B, '78' TMP_C, '1' TMP_D, '2' TMP_E from dual union all
select '1004' as TMP_A, 'A1235' TMP_B, '79' TMP_C, '1' TMP_D, '2' TMP_E from dual union all
select '1004' as TMP_A, 'A1235' TMP_B, '80' TMP_C, '2' TMP_D, '2' TMP_E from dual

꼭 좀 부탁드릴께요 ㅠㅠ

처음부터 다시 공부해야할거 같아요 흑흑