Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE TABLE_A(Int_no INT, ch_dt DATE, cl_no VARCHAR2(15), mod_dat DATE);
INSERT INTO TABLE_A
SELECT 1,date'2018-06-21','A_01',date'2017-01-01' FROM dual UNION ALL
SELECT 2,NULL            ,'A_07',date'2017-03-08' FROM dual UNION ALL
SELECT 3,NULL            ,'A_02',date'2017-02-11' FROM dual UNION ALL
SELECT 4,date'2018-06-13','A_01',date'2017-01-29' FROM dual;
4 rows affected

CREATE TABLE TABLE_B(cl_no VARCHAR2(15), created_by VARCHAR2(15), mat_id VARCHAR2(15));
INSERT INTO TABLE_B
SELECT 'A_01','A','09' FROM dual UNION ALL
SELECT 'A_04','B','07' FROM dual UNION ALL
SELECT 'A_07','D','04' FROM dual UNION ALL
SELECT 'A_02','X','01' FROM dual;
4 rows affected

CREATE TABLE TABLE_C(mat_id VARCHAR2(15), ch_dd_dt DATE);
INSERT INTO TABLE_C
SELECT '09', NULL             FROM dual UNION ALL
SELECT '07', date'2018-06-02' FROM dual UNION ALL
SELECT '15', NULL             FROM dual UNION ALL
SELECT '04', NULL             FROM dual UNION ALL
SELECT '07', date'2018-08-08' FROM dual;
5 rows affected

SELECT * FROM TABLE_C;


MAT_ID
CH_DD_DT


09
null

07
02-JUN-18

15
null

04
null

07
08-AUG-18


UPDATE TABLE_C c
   SET c.ch_dd_dt =
       (SELECT NVL(a.ch_dt, a.mod_dat)
          FROM TABLE_A a
          JOIN TABLE_B b
            ON b.cl_no = a.cl_no
         WHERE a.ch_dt IS NULL
           AND b.mat_id = c.mat_id)
 WHERE EXISTS (SELECT NVL(a.ch_dt, a.mod_dat)
                 FROM TABLE_A a
                 JOIN TABLE_B b
                   ON b.cl_no = a.cl_no
                WHERE a.ch_dt IS NULL
                  AND b.mat_id = c.mat_id)
                  
1 rows affected

SELECT * FROM TABLE_C;


MAT_ID
CH_DD_DT


09
null

07
02-JUN-18

15
null

04
08-MAR-17

07
08-AUG-18