Update from another table with multiple condition

Question

I having following tables

TABLE_A
Int_no        ch_dt      cl_no    mod_dat
1           21-06-18      A_01    01-01-2017
2           NULL          A_07    08-03-2017
3           NULL          A_02    11-02-2017
4           13-06-18      A_01    29-01-2017

TABLE_B
cl_no      created_by      mat_id
A_01            A             09
A_04            B             07
A_07            D             04
A_02            X             01

TABLE_C
mat_id       ch_dd_dt 
09            NULL
07            02-06-18
15            NULL
04            NULL
07            08-08-18

I want to update TABLE C ch_dd_dt column with TABLE A ch_dt column. Update should have a condition of if ch_dt from TABLE A is NULL then ch_dd_dt from TABLE C should update with mod_dat column Else ch_dd_dt column should update with ch_dt column.

I tried with below query but wont work

UPDATE TABLE_C cp
   SET cp.ch_dd_dt = CASE
                       WHEN (SELECT NVL(to_char(hc.ch_dt, 'ddmmyyyy'),
                                        '01012020')
                               FROM TABLE_A hc
                              WHERE ch_dt IS NULL) = '01012020' THEN
                        (SELECT hc.mod_dat
                           FROM TABLE_A hc
                          WHERE hc.cl_no IN
                                (SELECT i.cl_no
                                   FROM TABLE_B i
                                   JOIN TABLE_A x
                                     ON i.cl_no = x.cl_no))
                       ELSE
                        (SELECT hc.ch_dt
                           FROM TABLE_A hc
                          WHERE hc.cl_no IN
                                (SELECT i.cl_no
                                   FROM TABLE_B i
                                   JOIN TABLE_A x
                                     ON i.cl_no = x.cl_no))
                     END;

Answer 1

A common column is needed from TABLE_C with the others for matching condition, that seems to be mat_id with TABLE_B.

A subquery needed, which should include the NULL vs. NOT NULL cases ( NVL(a.ch_dt, a.mod_dat) ) , to be used to bring the counterpart value for ch_dd_dt column, and after EXISTS clause.

Therefore I constructed such a query :

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)

which only updates the row of TABLE_C with mat_id = '04'

Demo

Alternatively, you can use a MERGE Statement including MATCHED case only :

MERGE INTO TABLE_C c
  USING
       (SELECT NVL(a.ch_dt, a.mod_dat) AS ch_dt, b.mat_id
          FROM TABLE_A a
          JOIN TABLE_B b
            ON b.cl_no = a.cl_no
         WHERE a.ch_dt IS NULL) ab
           ON ( ab.mat_id = c.mat_id)
 WHEN MATCHED THEN UPDATE SET c.ch_dd_dt = ab.ch_dt  

Demo

Answer 2

You can't use Joins in Update query directly, you have to use either Correlated update or Key preserved view.

Below query should work.

 UPDATE (SELECT cp.ch_dd_dt  ch_dd_dt,
                    hc.ch_dt,
                    hc.mod_dat,
                    hc.ch_dt,
                    i.cl_no,
                    x.cl_no,
                    cp.mat_id,
                    i.mat_id      
            FROM TABLE_C cp INNER JOIN TABLE_b i
            ON (cp.mat_id = i.mat_id)
            INNER JOIN TABLE_A x
            ON (i.cl_no = x.cl_no))
        set ch_dd_dt = CASE WHEN  NVL(to_char(hc.ch_dt,'ddmmyyyy'), '01012020') and  hc.ch_dt IS NULL) = '01012020'
                            THEN hc.mod_dat
                            ELSE hc.ch_dt END;