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 varchar2(10)) from dual
union all
select 'JIM', date '1991-11-30', '23.2', null from dual
union all
select 'JIM', date '1991-11-30', '23.3', null from dual
union all
select 'TOM', date '1993-12-30', '30.1', null from dual
union all
select 'TOM', date '1993-12-30', '30.3', null from dual
union all
select 'HENRY', date '1993-12-30', '34.1', null from dual
union all
select 'HENRY', date '1994-12-03', cast('34.' || (level + 4) as varchar2(10)), null
from dual
connect by level <= 12;
18 rows affected

merge into table1
using (
  select name, dob, id1,
    to_char(100 + dense_rank() over (order by name, dob))
        ||'.'||
        dense_rank() over (partition by name, dob
          order by to_number(substr(id1, instr(id1, '.') + 1)))
        as id2
  from table1
) tmp on (tmp.id1 = table1.id1)
when matched then
update set table1.id2 = tmp.id2;
18 rows affected

select * from table1;


NAME
DOB
ID1
ID2


JIM
30-NOV-91
23.1
103.1

JIM
30-NOV-91
23.2
103.2

JIM
30-NOV-91
23.3
103.3

TOM
30-DEC-93
30.1
104.1

TOM
30-DEC-93
30.3
104.2

HENRY
30-DEC-93
34.1
101.1

HENRY
03-DEC-94
34.5
102.1

HENRY
03-DEC-94
34.6
102.2

HENRY
03-DEC-94
34.7
102.3

HENRY
03-DEC-94
34.8
102.4

HENRY
03-DEC-94
34.9
102.5

HENRY
03-DEC-94
34.10
102.6

HENRY
03-DEC-94
34.11
102.7

HENRY
03-DEC-94
34.12
102.8

HENRY
03-DEC-94
34.13
102.9

HENRY
03-DEC-94
34.14
102.10

HENRY
03-DEC-94
34.15
102.11

HENRY
03-DEC-94
34.16
102.12

NAME	DOB	ID1	ID2
JIM	30-NOV-91	23.1	103.1
JIM	30-NOV-91	23.2	103.2
JIM	30-NOV-91	23.3	103.3
TOM	30-DEC-93	30.1	104.1
TOM	30-DEC-93	30.3	104.2
HENRY	30-DEC-93	34.1	101.1
HENRY	03-DEC-94	34.5	102.1
HENRY	03-DEC-94	34.6	102.2
HENRY	03-DEC-94	34.7	102.3
HENRY	03-DEC-94	34.8	102.4
HENRY	03-DEC-94	34.9	102.5
HENRY	03-DEC-94	34.10	102.6
HENRY	03-DEC-94	34.11	102.7
HENRY	03-DEC-94	34.12	102.8
HENRY	03-DEC-94	34.13	102.9
HENRY	03-DEC-94	34.14	102.10
HENRY	03-DEC-94	34.15	102.11
HENRY	03-DEC-94	34.16	102.12