MySQL UPDATE multiple fields under the same condition (IF or CASE)

Question

MySQL UPDATE multiple fields under the same condition (IF or CASE)

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

Friends, I need help.

I have these 2 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 perfectly and there are no significant differences in runtime. The problem is that I need to update multiple fields, not just one.

What is the syntax for updating multiple fields? Will I have to repeat the condition for each field?

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

In advance thank you.

mysql sql-update

asked by anonymous 26.03.2015 / 19:57

2 answers

Identify the positions of objects How to execute a method inside a textBox only if the user give enter?

score 1 · Answer 1

The syntax for multiple fields is:

UPDATE [tabela]
    SET campo1 = valor1,
        campo2 = valor2 
        ...
WHERE ...

As for your query, it looks like it could be reformulated like this:

UPDATE leilaov
  SET seconds = seconds-1
  -- outros campos aqui conforme necessário
WHERE numero12345 = 1
  AND (
     (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()))
  )

score 0 · Answer 2

You can try to create a function:

    DELIMITER ;;

    CREATE FUNCTION obterSeconds (horaini INT, diaini INT, mesini INT, seconds INT) 
    RETURNS INT
    BEGIN
        RETURN 
            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;;
    DELIMITER ;

and call it like this:

UPDATE leilaov
  SET seconds = obterSeconds(horaini, diaini, mesini, seconds) 
WHERE numero12345 = 1

But I leave my alert, your CASE does not have an ELSE statement and in case none of the conditions are true will post NULL, I do not know if this is what I want, if seconds is NOT NULL the application will get an error SQL statement saying that seconds can not receive a null value (and you can ignore it) OR if seconds is NULLABLE maybe you wanted to add some conditions in the WHERE clause of your update. Or, maybe add an ELSE in the CASE statement.