Oracle 11g Release 2

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

CREATE TABLE tableX
(
 REFNO  INT          NOT NULL,
 SEQNO  INT          NULL,
 NAME   VARCHAR(200) NOT NULL,
 STARTD DATE         NOT NULL,
 PRIMARY KEY (REFNO, STARTD)
);
INSERT INTO tableX  (REFNO, NAME, STARTD) VALUES (10001, 'Anne', DATE'2018-10-03') ;
1 rows affected

INSERT INTO tableX  (REFNO, NAME, STARTD) VALUES (10001, 'Fe', DATE'2018-10-04') ;
1 rows affected

INSERT INTO tableX  (REFNO, NAME, STARTD) VALUES (10001, 'Lem', DATE'2018-10-05') ;
1 rows affected

INSERT INTO tableX  (REFNO, NAME, STARTD) VALUES (10001, 'Far', DATE'2018-10-06') ;
1 rows affected

INSERT INTO tableX  (REFNO, NAME, STARTD) VALUES (10002, 'Pit', DATE'2018-10-04') ;
1 rows affected

select t.* from tableX t ;


REFNO
SEQNO
NAME
STARTD


10001
null
Anne
03-OCT-18

10001
null
Fe
04-OCT-18

10001
null
Lem
05-OCT-18

10001
null
Far
06-OCT-18

10002
null
Pit
04-OCT-18


merge 
  into tableX t
  using 
  ( select t.*, 
           row_number() over (partition by refno order by startd) as rn
    from tableX t
  ) r
  on ( t.refno = r.refno
   and t.startd = r.startd
     )
when matched
then update
  set
   seqno = r.rn ;
5 rows affected

select t.* from tableX t ;


REFNO
SEQNO
NAME
STARTD


10001
1
Anne
03-OCT-18

10001
2
Fe
04-OCT-18

10001
3
Lem
05-OCT-18

10001
4
Far
06-OCT-18

10002
1
Pit
04-OCT-18

REFNO	SEQNO	NAME	STARTD
10001	null	Anne	03-OCT-18
10001	null	Fe	04-OCT-18
10001	null	Lem	05-OCT-18
10001	null	Far	06-OCT-18
10002	null	Pit	04-OCT-18