I tried to using the MySQL IF() function to execute a block of code based on a specified condition.

Return "KO" if the condition is TRUE, or "OK" if the condition is FALSE.

The following illustrates the syntax of the IF() function:

SELECT
id,
IF(DATEDIFF( Date_PEC, Date_Start ) > 30, "KO", "OK") DateDiff_1,
IF(DATEDIFF( Date_PEC, Data_Analysis ) > 45, "KO", "OK") DateDiff_2,
IF(DATEDIFF( Data_Analysis, Date_communication ) > 30, "KO", "OK") DateDiff_3,
IF(DATEDIFF( Date_communication, Date_of_issue ) > 30, "KO", "OK") DateDiff_4,
IF(DATEDIFF( Data_Analysis, Date_Archiving ) > 30, "KO", "OK") DateDiff_5,
IF(DATEDIFF( Date_of_issue, Date_Payment ) > 30, "KO", "OK") DateDiff_6 
FROM `dotable` 
WHERE
 id = 161016;

 -------- ------------ ------------ ------------ ------------ ------------ ------------ 
| id     | DateDiff_1 | DateDiff_2 | DateDiff_3 | DateDiff_4 | DateDiff_5 | DateDiff_6 |
 -------- ------------ ------------ ------------ ------------ ------------ ------------ 
| 161016 | OK         | OK         | OK         | OK         | OK         | OK         |
 -------- ------------ ------------ ------------ ------------ ------------ ------------ 
1 row in set (0.16 sec)

I need:

1. If all conditions return OK I have to update, for id number 161016, the ALS column of my table with OK value.
2. If at least one returned value is KO I have to update, for id number 161016, the ALS column of my table with KO value.

The version of the mysql database is 8.0.12.

Any suggestion?

CodePudding user response：

UPDATE dotable
SET als = CASE WHEN (DATEDIFF( Date_PEC, Date_Start ) > 30) 
                    (DATEDIFF( Date_PEC, Data_Analysis ) > 45) 
                    .. 
                    (DATEDIFF( Date_of_issue, Date_Payment ) > 30)
                THEN 'KO'
                ELSE 'OK'
                END
WHERE id = 161016;

Single (DATEDIFF( Date_PEC, Date_Start ) > 30) returns 0 or 1 depends on the condition is FALSE or TRUE.

When all 6 values are zero then the sum is zero and it is treated as FALSE which causes OK assigning.

When at least one value is not zero then the sum is above zero and it is treated as TRUE which causes KO assigning.

If some date value is NULL (or the value is not a date and strict mode is disabled) then OK will be assigned.

PS. I'd prefer to use generated column for als column instead of explicitly updated one - in this case you'll simply select actual value always.