Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table table1 (name, dob, id1, id2) as
select 'JIM', date '1991-11-30', 23.1, cast(null as number) from dual
union all
select 'JIM', date '1991-11-30', 23.2, cast(null as number) from dual
union all
select 'JIM', date '1991-11-30', 23.3, cast(null as number) from dual
union all
select 'TOM', date '1993-12-30', 30.1, cast(null as number) from dual
union all
select 'TOM', date '1993-12-30', 30.2, cast(null as number) from dual
union all
select 'HENRY', date '1994-12-03', 34.1, cast(null as number) from dual
union all
select 'HENRY', date '1994-12-03', 34.2, cast(null as number) from dual;
7 rows affected

select name, dob, id1,
  100 + dense_rank() over (order by trunc(id1))
      + dense_rank() over (partition by trunc(id1) order by id1)/10
      as id2
from table1;


NAME
DOB
ID1
ID2


JIM
30-NOV-91
23.1
101.1

JIM
30-NOV-91
23.2
101.2

JIM
30-NOV-91
23.3
101.3

TOM
30-DEC-93
30.1
102.1

TOM
30-DEC-93
30.2
102.2

HENRY
03-DEC-94
34.1
103.1

HENRY
03-DEC-94
34.2
103.2


merge into table1
using (
  select name, dob, id1,
    100 + dense_rank() over (order by trunc(id1))
        + dense_rank() over (partition by trunc(id1) order by id1)/10
        as id2
  from table1
) tmp on (tmp.id1 = table1.id1)
when matched then
update set table1.id2 = tmp.id2;
7 rows affected

select * from table1;


NAME
DOB
ID1
ID2


JIM
30-NOV-91
23.1
101.1

JIM
30-NOV-91
23.2
101.2

JIM
30-NOV-91
23.3
101.3

TOM
30-DEC-93
30.1
102.1

TOM
30-DEC-93
30.2
102.2

HENRY
03-DEC-94
34.1
103.1

HENRY
03-DEC-94
34.2
103.2

NAME	DOB	ID1	ID2
JIM	30-NOV-91	23.1	101.1
JIM	30-NOV-91	23.2	101.2
JIM	30-NOV-91	23.3	101.3
TOM	30-DEC-93	30.1	102.1
TOM	30-DEC-93	30.2	102.2
HENRY	03-DEC-94	34.1	103.1
HENRY	03-DEC-94	34.2	103.2