how to update and insert on specific condition in MySQL?

Question

I have one table name 'test':

create table test(main_mix_number varchar(20),formulation_mix_number varchar(20),param1 varchar(20),param2 varchar(20));

It has below row:

insert into test(formulation_mix_number,param1,param2) values ('101A','abc','xyz');
+-----------------+------------------------+--------+--------+
| main_mix_number | formulation_mix_number | param1 | param2 |
+-----------------+------------------------+--------+--------+
| NULL            | 101A                   | abc    | xyz    |
+-----------------+------------------------+--------+--------+

Now,

if number of row where 'main_mix_number' is 'NULL' equals 1 then I want to update the row 

else if number of rows where 'main_mix_number' is 'NULL' greater than 1 then I want to update the first row 

else if number of rows where 'main_mix_number' is 'NULL' equals 0 then row insertion took place.

So, I have tried the below query:

    select (
case when (select count(distinct(formulation_mix_number)) from test where formulation_mix_number='101A' and main_mix_number is NULL)=1 then 
(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 
(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 
(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 temp;

But as select and update or insert will not going to work together, this query is not working. I know there is another way that I can keep formulation_mix_number and main_mix_number in a separate table and do not mix main_mix_number with formulation_mix_number details. But due to some reason I am not allowed to add another table in my database.

Answer 1

this needs dynamic sql

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

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE stmt;

SELECT * FROM test

main_mix_number | formulation_mix_number | param1 | param2
:-------------- | :--------------------- | :----- | :-----
1A              | 101A                   | abc    | xyz   

db<>fiddle here