0

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

link 2

MySQL string replace

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.

LeeZee
  • 95
  • 1
  • 8

1 Answers1

2

It looks like you are confusing select and update. If you want to alter the actual data in the atbats_pitches_python_new then you want to be doing an update. Do a conditional update of the retro_game_id field when its corresponding id field ends in 2:

UPDATE atbats_pitches_python_new
SET retro_game_id = CONCAT(LEFT(retro_game_id, CHAR_LENGTH(retro_game_id) - 1), '2')
WHERE
    RIGHT(id, 1) = '2';

The trick here is to take a substring of retro_game_id including all characters except for the last one. Then, concatenate this substring with 2.

Tim Biegeleisen
  • 387,723
  • 20
  • 200
  • 263