I have a table with colums and data as follows
<code>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
</code>
<code>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
</code>
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
A simple option is to use case expression.
As you didn’t specify columns’ datatypes, I simplifed this example and used strings.
Before:
<code>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
</code>
<code>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
</code>
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:
<code>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.
</code>
<code>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.
</code>
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:
<code>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>
</code>
<code>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>
</code>
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>
9