I have a scenerio to update a table data

Question

I have a table with colums and data as follows

id    from_date      to_date   value  change_date
10    jan-2021      dec 2021    100    dec-2021
10    jan-2022      dec 2022    100    dec-2022
10    jan-2023      dec 2023    100    dec-2023

I have to update value of 100 to 200 for jan-2023 period at the same time I need to update change_date of jan-2022 and jan-2021 records from dec 2022 to nov 2022 and dec 2021 to nov 2021 in a single query

Answer 1

A simple option is to use case expression.

As you didn't specify columns' datatypes, I simplifed this example and used strings.

Before:

SQL> select * from test order by from_date;

        ID FROM_DAT TO_DATE       VALUE CHANGE_D
---------- -------- -------- ---------- --------
        10 jan-2021 dec-2021        100 dec-2021
        10 jan-2022 dec-2022        100 dec-2022
        10 jan-2023 dec-2023        100 dec-2023

Update:

SQL> update test set
  2    value = case when from_date = 'jan-2023' then 200 else value end,
  3    change_date = case when from_date = 'jan-2021' then 'nov-2021'
  4                       when from_date = 'jan-2022' then 'nov-2022'
  5                       else change_date
  6                  end;

3 rows updated.

After:

SQL> select * from test order by from_date;

        ID FROM_DAT TO_DATE       VALUE CHANGE_D
---------- -------- -------- ---------- --------
        10 jan-2021 dec-2021        100 nov-2021
        10 jan-2022 dec-2022        100 nov-2022
        10 jan-2023 dec-2023        200 dec-2023

SQL>