MySQL IF() function to execute a block of code based on a specified condition

Question

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:

If all conditions return OK I have to update, for id number 161016, the ALS column of my table with OK value.
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?

Update ALS column? Please add table definition. Also generally speaking storing a column which can be derived is not encouraged and have you considered a generated column? — P.Salmon
– P.Salmon, Commented May 27, 2022 at 7:57
@Akina I'm sorry. You have right, I have edit the question replacing IF statement with IF() function. Thanks for help. — Lando Marossi
– Lando Marossi, Commented May 27, 2022 at 8:17

Akina · Accepted Answer · 2022-05-27 07:58:42Z

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.

Collectives™ on Stack Overflow

MySQL IF() function to execute a block of code based on a specified condition

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related