Q:

CASE in UPDATE for choose a column

Hello i want to make a request to choose the column will be updated
UPDATE `ze_mail` SET 
        (CASE
            WHEN `ze_mail_id` = 1 AND `ze_mail_user1_id` = 27
            THEN(
                `ze_mail_user1_lastDate`
            )
            WHEN `ze_mail_id` = 1 AND `ze_mail_user2_id` = 27
            THEN(
                `ze_mail_user2_lastDate`
            )
        END) 
        = NOW()
WHERE WHEN `ze_mail_id` = 1

for obtaining that
UPDATE `ze_mail` SET `ze_mail_user1_lastDate` = NOW() WHERE `ze_mail_id` = 1

or that
UPDATE `ze_mail` SET `ze_mail_user2_lastDate` = NOW() WHERE `ze_mail_id` = 1

Can you help me?

A:

You can just 'update' both, and use CASE EXPRESSION so the result won't change if it doesn't answer the condition :
UPDATE `ze_mail`
  SET `ze_mail_user1_lastDate` = CASE WHEN `ze_mail_user1_id` = 27
                                      THEN NOW() 
                                      ELSE `ze_mail_user1_lastDate`
                                 END,
       `ze_mail_user2_lastDate` = CASE WHEN `ze_mail_user2_id` = 27
                                      THEN NOW() 
                                      ELSE `ze_mail_user2_lastDate`
                                 END
WHERE `ze_mail_id` = 1

Notes:
WHERE WHEN is wrong, a simple WHERE is enough.
A CASE is an expression, it can only provide a value .
You don't need to use ze_mail_id = 1 on the CASE EXPRESSION because you are filtering them already.

