mysql update with if and max

I have lost hours on this and nothing works for me.

I have filed strp_aa that is default NULL. The filed strp_aa should update only if its null with MAX strp_aa + 1, and if its not null if it already has a number it should stay the same.

short version of code is

UPDATE STRANKEP
SET strp_aa = IF(strp_aa=null, strp_aa+1, strp_aa)
WHERE strp_ID=36;

Also tired

UPDATE STRANKEP
                SET strp_aa = IF(strp_aa=null, (SELECT MAX(strp_aa)) +1, (SELECT (strp_aa) WHERE strp_ID=36)
                WHERE strp_ID=36;

I tried multiple things like this one mentioned here Update MySQL with if condition:

UPDATE STRANKEP
SET strp_aa = CASE WHEN strp_aa = NULL THEN  MAX(strp_aa) + 1 ELSE strp_aa END
WHERE strp_ID = 36;

I have also found this mysql query to update field to max(field) + 1 and tried all sorts of combinations with supplied answer and it wont work for me. One of the version is:

    UPDATE STRANKEP
                    SET strp_aa = IF((SELECT strp_aa )!=null,((SELECT selected_value2 FROM (SELECT (strp_aa) AS selected_value2 FROM STRANKEP WHERE strp_ID=36) AS sub_selected_value2)), ((SELECT selected_value FROM (SELECT MAX(strp_aa) AS selected_value FROM STRANKEP) AS sub_selected_value) + 1)  )
                    WHERE strp_ID=36;

This just keep adding one even if there is a number set on strp_aa… I don’t know what else to try.

EDIT: Had s little problem with @GMB answer because all fields are starting with NULL, so max(strp_aa) gives 0 results in case none of the fields had a number in it. I solved that with COALESCE statement and posting it here if someone has similar problem.

UPDATE STRANKEP t
CROSS JOIN (select COALESCE(MAX(strp_aa),0) max_strp_aa from STRANKEP) m
set t.strp_aa = m.max_strp_aa + 1
where t.strp_ID = 36 and t.strp_aa is null

Answer

You can use the update ... join syntax for this:

update strankep s
cross join (select max(strp_aa) max_strp_aa from strankep) m
set s.strp_aa = m.max_strp_aa + 1
where s.strp_id = 36 and s.strp_aa is null

The cross join brings the max value of strp_aa over the whole table. The where clause eliminate row(s) where strp_aa is not null.

Advertisement

Answer