Update multiple records once at a time based on some conditions-CodePudding

There are many wrong values in oracle table like -

b_id                   p_id      date
----------             ----      ----
ba_2020_xyz_jan_2021   xy_2021   01/01/2021
ba_2020_abc_jan_2021   ab_2021   01/01/2021
ba_2020_xyz_feb_2021   xy_2021   01/02/2021
ba_2020_abc_feb_2021   ab_2021   01/02/2021
ba_2020_xyz_mar_2021   xy_2021   01/03/2021
ba_2020_abc_mar_2021   ab_2021   01/03/2021
.......................

I want to update all these b_id values having year 2020 to 2021 like below -

b_id                   p_id      date
----------             ----      ----
ba_2021_xyz_jan_2021   xy_2021   01/01/2021
ba_2021_abc_jan_2021   ab_2021   01/01/2021
ba_2021_xyz_feb_2021   xy_2021   01/02/2021
ba_2021_abc_feb_2021   ab_2021   01/02/2021
ba_2021_xyz_mar_2021   xy_2021   01/03/2021
ba_2021_abc_mar_2021   ab_2021   01/03/2021
.......................

Could you please help me to update to above values.

CodePudding user response：

It seems you simply need a REPLACE function here -

UPDATE YOUR_TABLE
   SET b_id = REPLACE(b_id, '2020', '2021')
 WHERE b_id LIKE ' 20%'