UPDATE query with CASE WHEN in Oracle

user_15137280 0
Viewed 70

I have a table TABLE1 with 3 columns NAME, ROLLNO, CASHDATE.

CREATE TABLE TABLE1
(
    NAME VARCHAR2(4) NOT NULL, 
    ROLLNO NUMBER(4) NOT NULL, 
    CASHDATE VARCHAR2(8) NOT NULL
);

INSERT INTO TABLE1 VALUES('SAMY', 1234, '15990101');
INSERT INTO TABLE1 VALUES('TOMY', 1324, '15990101');
INSERT INTO TABLE1 VALUES('DANY', 1342, '15990101');

TABLE1 looks like:

NAME         ROLLNO      CASHDATE
----------------------------------
SAMY         1234        15990101
TOMY         1324        15990101
DANY         1342        15990101

CASHDATE value is in the form of YYYYMMDD

I have a second table TABLE2 with 3 columns NAME, ID, ID_DATE:

CREATE TABLE TABLE2
(
     NAME VARCHAR2(4) NOT NULL, 
     ID VARCHAR2(2) NOT NULL, 
     ID_DATE TIMESTAMP(3)
);

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');

TABLE2 looks like:

NAME         ID        ID_DATE
--------------------------------------------------------
SAMY         01        21-AUG-2021 12.00.00.000000000 AM
SAMY         A1        19-AUG-2018 12.00.00.000000000 AM
TOMY         01        22-AUG-2021 12.00.00.000000000 AM 
TOMY         B1        15-AUG-2000 12.00.00.000000000 AM

And I have a third table TABLE3 with 2 columns NAME, SEC_DATE:

CREATE TABLE TABLE3 
(
    NAME VARCHAR2(4) NOT NULL, 
    SEC_DATE TIMESTAMP(3)
);

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');

TABLE3 looks like:

NAME         SEC_DATE
----------------------------------------------
SAMY         21-AUG-2021 12.00.00.000000000 AM
TOMY         22-AUG-2021 12.00.00.000000000 AM 
DANY         29-AUG-2021 12.00.00.000000000 AM

As we see I have a TABLE1 having CASHDATE value as 15990101 which is default value.

So we need to UPDATE CASHDATE column which is having 15990101 in TABLE1 based on following conditions.

  1. First it checks in TABLE2 based on NAME in TABLE2, if there is a record having same NAME with ID = '01' then ID_DATE value should update in CASHDATE column of TABLE1.
  2. If it is not found in TABLE2 based on NAME with ID = '01', then it checks in TABLE3 and based on NAME only if there is a record, we need to update (SEC_DATE - 1) value in CASHDATE column of TABLE1.

Finally after update, the result TABLE1 looks like:

 NAME         ROLLNO      CASHDATE
----------------------------------
 SAMY         1234        20210821   --This record found in TABLE2
 TOMY         1324        20210822   --This record found in TABLE2
 DANY         1342        20210828   --This record found in TABLE3 (SEC_DATE - 1)

I understand we need to update statement but I am not sure using CASE WHEN in UPDATE statement.

oracle11g oracle sql
3 Answers

You can use COALESCE in combination with subqueries for this:

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';

Demo: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=b6c5b08f5b303a23d26e5349228ee301

user_22707620

From my point of view, a simple option is a two-step option, i.e. two updates. In this case, I actually prefer MERGE over UPDATE.

Initial table1 contents:

SQL> select * From table1;

NAME     ROLLNO CASHDATE
---- ---------- --------
SAMY       1234 15990101
TOMY       1324 15990101
DANY       1342 15990101

Update cashdate based on table2's contents (that's your 1st condition):

SQL> merge into table1 a
  2    using table2 b
  3    on (a.name = b.name)
  4    when matched then update set
  5      a.cashdate = to_char(b.id_date, 'yyyymmdd')
  6      where a.cashdate = '15990101'
  7        and b.id = '01';

2 rows merged.

Update cashdate based on table3's contents (that's your 2st condition) (not exists is here to skip rows already updated in previous step):

SQL> merge into table1 a
  2    using table3 c
  3    on (c.name = a.name)
  4    when matched then update set
  5      a.cashdate = to_char(c.sec_date - 1, 'yyyymmdd')
  6      where a.cashdate = '15990101'
  7        and not exists (select null from table2 b
  8                        where b.name = a.name
  9                          and b.id = '01'
 10                       );

1 row merged.

Final result:

SQL> select * from table1;

NAME     ROLLNO CASHDATE
---- ---------- --------
SAMY       1234 20210821
TOMY       1324 20210822
DANY       1342 20210828

SQL>
user_90979060

You can query the data and update the table rows from the query result. This is called an updateable query. The great advantage is that we only update rows that we want updated (in your case rows that are on default value and have a match in table2 and/or table3).

For a query to be updateable, Oracle must see it guaranteed that there is just one target value selected per row. This means that we need unique constraints to ensure that joining table2 and table3 rows to a table1 row still results in no more than one row per table1 row.

The constraints needed here are

  • table2: primary key (name, id)
  • table3: primary key (name)

With these constraints in place, Oracle should be able to update our query:

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');

This may still fail. It really depends on whether Oracle considers this query updateable. It should (and it does in Oracle 18c, as you can see in the demo inked below), but sometimes Oracle struggles with such queries. You'll have to try whether this already works in Oracle 11g.

Demo: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=36287406a7f5591d4b53df4a7b990ef6

user_22707620
Related