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

Asked

Viewed 1,648 times

1

Friends, I need help.

I have these two darlings:

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 and not just one.

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

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

Thank you in advance.

mysql sql-update

  • By multiple fields you mean multiple records with the numero12345 different?

  • No @Luis Henrique. In the example above I am updating the Seconds field. I need to update other fields (eg mesini and diaini) from the same record. Thank you

2 answers

1

The syntax for multiple fields is:

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

As for your query, it seems that 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()))
  )

0

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 that:

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, do not know if this is the intended one, if Seconds is NOT NULL the application will get an SQL error saying that Seconds cannot receive a null value (and Voce can ignore) OR if Seconds is NULLABLE maybe Voce wanted to add some conditions in the WHERE clause of your update. Or, maybe add a ELSE to the CASE statement.

Browser other questions tagged mysql sql-update

You are not signed in. Login or sign up in order to post.