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, primary key (name));
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), primary key (name, id));
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), primary key (name));
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
(
  select
    t1.cashdate,
    coalesce(t2.id_date, t3.sec_date - interval '1' day) as found_date
  from table1 t1
  left join table2 t2 on t2.name = t1.name and t2.id = '01'
  left join table3 t3 on t3.name = t1.name
  where t1.cashdate = '15990101'
  and coalesce(t2.id_date, t3.sec_date - interval '1' day) is not null
)
set cashdate = to_char(found_date, 'yyyymmdd');
3 rows affected

select * from table1;


NAME
ROLLNO
CASHDATE


SAMY
1234
20210821

TOMY
1324
20210822

DANY
1342
20210828