UPDATE with different conditions in the same query

Asked

Viewed 1,085 times

1

How to update several records by setting different values, example:

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

then I just want Jose to receive the 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

1 answer

3


Can use a 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 except in cases where the advantage is really evident.

  • Thanks, I didn’t know you could use IF inside the query. But I see no other option for my system, in this case it is a group registration of students where only the leader of the group receives level 2 at the time of registration

  • This IF is a function, equivalent to the ternary of other languages expressão ? valor se verdadeiro : valor se falso - in this case, if the first parameter is true, returns the second, otherwise returns the third.

Browser other questions tagged php mysql pdo

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