Update table so that a field has a unique value

Asked

Viewed 57 times

1

How can I update a record in Mysql so that when I change a field of this record all other records change so that this field has a unique value? for example, I have a table called players and a field called artiheiro, that basically will receive a boolean value, only to say that this player is a scorer or not, and as there can be no more than one scorer player... when change some record for some record to artilheiro = 1 all other records automatically change to artilheiro = 0

mysql sql

  • It seems to me a strange solution to your problem, but if you want to solve it the way you yourself suggested you can create a trigger for that reason.

  • the first idea that occurred to me was: first update the current scorer to 0 and then update the scorer to 1

  • Why, Sorack? I should "reset" everyone before and update the specific?

  • Has any response helped solve the problem and can address similar questions from other users? If so, make sure to mark the answer as accepted. To do this just click on the left side of it (below the indicator of up and down votes).

1 answer

2


You can create a TRIGGER that does what has been described:

CREATE TRIGGER trg_jogadores_ai AFTER UPDATE ON jogadores
FOR EACH ROW
BEGIN
  IF (NEW.artilheiro = 1) THEN
    UPDATE jogadores
       SET artilheiro = 0
     WHERE id <> NEW.id
       AND artilheiro = 1;
END;

In the TRIGGER above you will check if the record has been changed to artilheiro = 1 and if this has occurred will UPDATE in all records that do not have the same id and who have artilheiro = 1 for 0.

Browser other questions tagged mysql sql

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