Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;


BANNER
BANNER_FULL
BANNER_LEGACY
CON_ID


Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
0


CREATE TABLE TABLE1( NAME VARCHAR2(4) NOT NULL, ROLLNO NUMBER(4) NOT NULL, CASHDATE VARCHAR2(8) NOT NULL);
begin
 INSERT INTO TABLE1 VALUES('SAMY', 1234, '15990101');
 INSERT INTO TABLE1 VALUES('TOMY', 1324, '15990101');
 INSERT INTO TABLE1 VALUES('DANY', 1342, '15990101');
end;
/
1 rows affected

CREATE TABLE TABLE2( NAME VARCHAR2(4) NOT NULL, ID VARCHAR2(2) NOT NULL, ID_DATE TIMESTAMP(3));
begin
 INSERT INTO TABLE2 VALUES('SAMY', '01', timestamp '2021-08-21 00:00:00');
 INSERT INTO TABLE2 VALUES('SAMY', 'A1', timestamp '2018-08-19 00:00:00');
 INSERT INTO TABLE2 VALUES('TOMY', '01', timestamp '2021-08-22 00:00:00');
 INSERT INTO TABLE2 VALUES('TOMY', 'B1', timestamp '2000-08-15 00:00:00');
end;
/
1 rows affected

CREATE TABLE TABLE3( NAME VARCHAR2(4) NOT NULL, SEC_DATE TIMESTAMP(3));
begin
 INSERT INTO TABLE3 VALUES('SAMY', timestamp '2021-08-21 00:00:00');
 INSERT INTO TABLE3 VALUES('TOMY', timestamp '2021-08-22 00:00:00');
 INSERT INTO TABLE3 VALUES('DANY', timestamp '2021-08-29 00:00:00');
end;
/
1 rows affected

update table1 t1
set cashdate =
  coalesce
  (
    (select to_char(id_date, 'yyyymmdd') from table2 t2 where t2.name = t1.name and t2.id = '01'),
    (select to_char(sec_date - interval '1' day, 'yyyymmdd') from table3 t3 where t3.name = t1.name),
    '15990101'
  )
where cashdate = '15990101';
3 rows affected

select * from table1;


NAME
ROLLNO
CASHDATE


SAMY
1234
20210821

TOMY
1324
20210822

DANY
1342
20210828