Count status until it changes

If you need to count events of given type until it changes the following query may help. For example failed charges before disabling an account.

UPDATE my_table SET 
status_count = CASE WHEN last_status = ‘MY_STATUS’ THEN status_count+1 ELSE 1 END,
next_attempt = NOW(),
last_status = 'MY_STATUS'
WHERE id = 123;