0

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
GMB
  • 216,147
  • 25
  • 84
  • 135
ikiK
  • 6,328
  • 4
  • 20
  • 40

1 Answers1

1

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.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • 1
    Oh my IT WORKS. I have lost half of day searching for anwser and didint come across CROSS JOIN, and i never used it before. Thank you very much! I will look into this function further! – ikiK Mar 14 '20 at 01:44
  • Yeah it makes total sense to eliminate it with WHERE it crossed my mind. – ikiK Mar 14 '20 at 01:53
  • Is it possible to make this work when there are no fields with numbers in it, MAX is failing when everything is NULL. I suppose it could be be done with some kind of CASE statement? – ikiK Mar 14 '20 at 04:48
  • I solved it with case and updated my post for everyone to see if interested. Thank you for help one more time. – ikiK Mar 14 '20 at 12:51