Execute an UPDATE in MySQL only when the number of rows equals 1

1

How to make a UPDATE only run when the number of rows that returned is equal to 1 in a single query only with SQL syntax using the example below?

Example:

SELECT count(*) AS rows FROM exemplo WHERE id_usuario = 100;

if (rows == 1)
{
    //executar UPDATE
}
    
asked by anonymous 03.02.2016 / 17:17

1 answer

0

CASE WHEN seems to me to be ideal here.

If the table you want to use is different from the table whose rows you need to check, you can do something similar to this:

UPDATE tabela2 SET campo=
   CASE WHEN (SELECT count(*) FROM exemplo WHERE id_usuario=100) = 1 THEN 
     'novovalor' -- novo valor para "campo" 
   ELSE 
     campo -- atualiza "campo" com o valor de "campo", nada muda
END;

If the target table of the update is the same one whose number of rows should be checked the thing is more complicated since MySQL does some internal madness that does not allow you to directly reference the same update table in the select inner query, but is still possible with a little trick:

UPDATE exemplo SET campo=
   CASE WHEN (SELECT count(*) FROM (
      SELECT * FROM exemplo WHERE id_usuario=100
   ) AS aliasqualquer) = 1 THEN 
     'novovalor' -- novo valor para "campo" 
   ELSE 
     campo -- atualiza "campo" com o valor de "campo", nada muda
END;
    
03.02.2016 / 17:39