Changing table values using UPDATE CASE

Asked

Viewed 112 times

1

hello, I have a table of this template: 

+-------------------------------+
|        participacao           |
+----+--------+------+----------+
| id | nome   | equipe | ativo  | 
+----+--------+--------+--------+
| 1  | luiz   |  A     |   1    |
+----+--------+--------+--------+
| 2  | carlos |  A     |   1    |
+----+--------+--------+--------+
| 3  | maria  |  C     |   1    |
+----+--------+--------+--------+
| 4  | maria  |  D     |   1    | 
+----+--------+--------+--------+
| 5  | carlos |  E     |   1    |
+----+--------+--------+--------+

i would like to update the values of the 'active' column in which when I changed the 'name' to a 'team' and this name appeared duplicated in this team occurred the update to '0' if you don’t let '1',
example: NAME='Luiz' instead of name='carlos' in team=A and in team=E

+-------------------------------+
|          participacao         |
+----+--------+--------+--------+
| id | nome   | equipe | ativo  | 
+----+--------+--------+--------+
| 1  | luiz   |  A     |   1    |
+----+--------+--------+--------+
| 2  | luiz   |  A     |   0    |
+----+--------+--------+--------+
| 3  | maria  |  C     |   1    |
+----+--------+--------+--------+
| 4  | bia    |  A     |   0    | 
+----+--------+--------+--------+
| 5  | luiz   |  E     |   1    |
+----+--------+--------+--------+

I’ve seen some examples and I’m applying it this way:

UPDATE participacao SET nome = CASE  
WHEN nome = carlos AND equipe = A THEN nome = luiz, ativo = 0  
WHEN nome = carlos AND equipe !=A THEN nome = luiz, ativo = 1  
END  
WHERE nome in (carlos);

but it’s not working
I accept suggestions, thank you

mysql sql-update

2 answers

1


First you must put the values in quotes (except the numeric ones). For example, instead of nome = carlos, use nome = 'carlos'.

And to change the two columns (nome and ativo) you make two CASE, first by changing the column ativo and then the column nome:

UPDATE participacao SET
ativo = CASE
WHEN nome = 'carlos' AND equipe = 'A' THEN 0
WHEN nome = 'carlos' AND equipe != 'A' THEN 1
END,
nome = CASE
WHEN nome = 'carlos' AND equipe = 'A' THEN 'luiz'
WHEN nome = 'carlos' AND equipe != 'A' THEN 'luiz'
END
WHERE nome IN ('carlos');

  • Sam thank you very much, I marked your reply as the one that took away many doubts and helped me in solving the problem, thanks for your time in helping!!

1

would be two commands:

UPDATE participacao SET ativo = 0 WHERE nome = 'luiz' AND equipe = 'A';
UPDATE participacao SET nome = 'carlos' WHERE id = 2

  • opa amigo thanks for your time, I needed not search for the id because the same in my table and 'AUTO INCREMENT', your answer helped me clarify many things!!

Browser other questions tagged mysql sql-update

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