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
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!!
– luiz_vitorr