Mysql updates the string based on the characters it contains

suppose a field is replaced with"1"at the beginning of"a "and" 2"at the beginning of"1". How to write an update statement is better
I have tried regexp, but then there are a lot of sql statements, and it will be very slow

.
UPDATE trade SET
transportal="1"
WHERE
transportal REGEXP "^(a)";

is there any way to optimize it

Apr.07,2021

if it is only count statistics, you do not need to do update operation.

I feel that your requirements use the calculated value of the case when expression, and then do count statistics, such as:

select trans_type, count(*)
from (
    select case when left(transportal,1) = 'a' then 1 when left(transportal,1) = 'b' then 2 else 0 end as trans_type
    from trade 
) t
group by trans_type

these should be controlled by the program, and SQL should not do such a thing

MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-1b2b2da-2ba85.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-1b2b2da-2ba85.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?