UPDATE with different conditions in the same query

Question

UPDATE with different conditions in the same query

Navigation

#1 by (3 votes)

1

How to update multiple records by setting different values, eg:

$update = "UPDATE tabela SET ativo=1, nivel=2 WHERE nome ='jose'
                                              AND nome='maria'
                                              AND nome='joao'";

In this case, I just want jose to receive level = 2 and the rest receive level = 1 in the same query. and active = 1 for all .

I'm using PHP with PDO.

php mysql pdo

asked by anonymous 18.04.2016 / 21:26

1 answer

Error installing ionic in ubuntu Background-color does not accompany content

score 3 · Accepted Answer

You can use IF()

UPDATE
   tabela
SET
   ativo = 1,
   nivel = IF( nome='jose', 2, 1 )
WHERE
   nome IN ( 'jose', 'maria', 'joao' );

I find it interesting as knowledge, but in practice do not create this type of update with many conditions other than in cases where the advantage really is evident.