I'm trying to replace the 12th character of a value in a field retro_game_id
with another character. The value is a string and the last character is '0'. I need to replace the '0' with '2' when the last character of another string value of another field in the same table id
is '2'. An example of the value of the first field is 'BAL200809230'. An example of a string in the other field is '2008/09/23/tbamlb-balmlb-2'
So using the above as an example, I need to change 'BAL200809230' to 'BAL200809232' of the retro_game_id
field only when the corresponding value of the id
field has a '2' at the end as in '2008/09/23/tbamlb-balmlb-2'
I tried the following code using the following links to guide me but to no avail: Replace last two characters in column
SELECT `retro_game_id` FROM atbats_pitches_python_new
SET `retro_game_id`= REPLACE(RIGHT(`retro_game_id`,1),'0','2')
WHERE RIGHT(`id`, 1) ='2
Can someone please assist with the correct code to accomplish this?
Thank you.