jeudi 26 mars 2015

UPDATE multiple fields under same condition (IF or CASE)

Need some help. There are some questions and answers here about this topic but didn't found one that fits my need.


I have these two queries:



UPDATE leilaov
SET seconds = CASE
WHEN (mesini = MONTH(NOW()) AND diaini = DAYOFMONTH(NOW()) AND horaini = HOUR(NOW()) AND minutoini <= MINUTE(NOW()))
OR (mesini = MONTH(NOW()) AND diaini < DAYOFMONTH(NOW()))
OR (mesini = MONTH(NOW()) AND diaini = DAYOFMONTH(NOW()) AND horaini < HOUR(NOW()))
OR (mesini < MONTH(NOW())) THEN seconds-1
END
WHERE numero12345 = 1


UPDATE leilaov
SET seconds = IF((mesini = MONTH(NOW()) AND diaini = DAYOFMONTH(NOW()) AND horaini = HOUR(NOW()) AND minutoini <= MINUTE(NOW()))
OR (mesini = MONTH(NOW()) AND diaini < DAYOFMONTH(NOW()))
OR (mesini = MONTH(NOW()) AND diaini = DAYOFMONTH(NOW()) AND horaini < HOUR(NOW()))
OR (mesini < MONTH(NOW())), seconds-1, seconds)
WHERE numero12345 = 1


Both work fine and there are no significant differences in execution time. The problem is that I need to update several fields and not just only one.


Whats the syntax to update several fields? Do I have to repeat the condition to each field?


Should I use CASE or IF? Or is there another better option?


Thanks in advance.


Aucun commentaire:

Enregistrer un commentaire