Update table so that a field has a unique value

Question

Update table so that a field has a unique value

Navigation

#1 by (2 votes)

0

How can I update a record in MySQL so that when I change a field in that 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 artifact , which basically will receive a boolean value, just to say that player is a top scorer and not there may be more than one top scorer ... when you change some record to artilheiro = 1 all other records automatically change to artilheiro = 0

mysql sql

asked by anonymous 07.02.2018 / 15:32

1 answer

Remove class when doing Scroll Up with JQuery How to format the select option with bootstrap in an angle?

score 2 · Accepted Answer

You can create a TRIGGER that does what is 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 above% you will verify that the record has been changed to TRIGGER and if this has occurred it will artilheiro = 1 in all records that do not have the same UPDATE and have id to artilheiro = 1 .