Optimizing a 1 Time SQL Update Statement In MariaDB

I am trying to update around 40 thousand records on a table named locations, with 6 columns total. The 40 thousand records fall into 1 of 6 categories. The categories are: Column "Bin" value ends in 'a', 'b', 'c', 'd', 'e', or none of the aforementioned.

I wrote the following Update statements with the intention of being ran directly on the table on the server.

UPDATE locations
SET Division=1, Bin=LEFT(Bin, LENGTH(Bin)-1)
WHERE RIGHT(Bin, 1)='a';

UPDATE locations
SET Division=2, Bin=LEFT(Bin, LENGTH(Bin)-1)
WHERE RIGHT(Bin, 1)='b';

UPDATE locations
SET Division=3, Bin=LEFT(Bin, LENGTH(Bin)-1)
WHERE RIGHT(Bin, 1)='c';

UPDATE locations
SET Division=4, Bin=LEFT(Bin, LENGTH(Bin)-1)
WHERE RIGHT(Bin, 1)='d';

UPDATE locations
SET Division=5, Bin=LEFT(Bin, LENGTH(Bin)-1)
WHERE RIGHT(Bin, 1)='e';

I have never updated more than 1-5 records at a time, and am unsure of whether there is a better way to do this.

I can do some coding with php, so if a solution would need to incorporate it, I'm open to the idea.

If I left out any relevant or needed information, let me know. Thanks for any and all help!

Solution

With just 40k records and a 1 off update I wouldn't worry too much about the performance, but you could try a single update statement:-

UPDATE locations
SET Division =  CASE RIGHT(Bin, 1)
                    WHEN 'a'
                    THEN 1
                    WHEN 'b'
                    THEN 2
                    WHEN 'c'
                    THEN 3
                    WHEN 'd'
                    THEN 4
                    ELSE 5
                END,
Bin = LEFT(Bin, LENGTH(Bin)-1)
WHERE RIGHT(Bin, 1) IN ('a', 'b', 'c', 'd', 'e');

Possible down side of you current solution is say a record had the value of the field bin as 'edcba'

Your first update you set Division to 1 and bin to 'edcb', but your next UPDATE would set the same row to a Division of 2 and bin of 'edc', etc