MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table test(main_mix_number varchar(20),formulation_mix_number varchar(20),param1 varchar(20),param2 varchar(20));
insert into test(formulation_mix_number,param1,param2) values ('101A','abc','xyz');
    SELECT 
    (CASE
        WHEN
            (SELECT 
                    COUNT(DISTINCT (formulation_mix_number))
                FROM
                    test
                WHERE
                    formulation_mix_number = '101A'
                        AND main_mix_number IS NULL) = 1
        THEN
            (@sql:='update test set main_mix_number=\'1A\' where formulation_mix_number=\'101A\'')
        WHEN
            (SELECT 
                    COUNT(DISTINCT (formulation_mix_number))
                FROM
                    test
                WHERE
                    formulation_mix_number = '101A'
                        AND main_mix_number IS NULL) > 1
        THEN
            (@sql:='update test set main_mix_number=\'1A\' where formulation_mix_number=\'101A\' order by formulation_mix_number desc limit 1')
        WHEN
            (SELECT 
                    COUNT(DISTINCT (formulation_mix_number))
                FROM
                    test
                WHERE
                    formulation_mix_number = '101A'
                        AND main_mix_number IS NULL) = 0
        THEN
            (@sql:='insert into test (main_mix_number,formulation_mix_number,param1,param2) 
            select * from (select \'1A\' as main_mix_number,\'101A\' as fromulation_mix_number,param1,param2 from test where formulation_mix_number=\'101A\' order by fromulation_mix_number desc limit 1) as tmp 
            where not exists (select main_mix_number,formulation_mix_number from test where main_mix_number=\'1A\' and formulation_mix_number=\'101A\')')
        ELSE NULL
    END)
FROM
    test


(CASE
        WHEN
            (SELECT 
                    COUNT(DISTINCT (formulation_mix_number))
                FROM
                    test
                WHERE
                    formulation_mix_number = '101A'
                        AND main_m


update test set main_mix_number='1A' where formulation_mix_number='101A'


PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE stmt;
SELECT * FROM test


main_mix_number
formulation_mix_number
param1
param2


1A
101A
abc
xyz