I'm not an expert in MySQL and I'm facing a problem which I don't know how to solve.
I've two tables.
Table `players`
id | name | high_score
---|------|-----------
1 | john | -
2 | mary | -
3 | mike | -
Table `scores`
id | id_player | score
---|-----------|------
1 | 1 | 12
2 | 1 | 5
3 | 3 | 8
4 | 2 | 7
5 | 2 | 25
6 | 3 | 18
UPDATE `productos_players` p INNER JOIN `scores` s ON (p.id = s.id_player) SET p.high_score=s.score
scores
You can try the following UPDATE
to update the high scores:
UPDATE `players` p
SET p.high_score = (
SELECT MAX(score)
FROM scores
WHERE id_player = p.id
);
Another solution would be to use a VIEW
:
CREATE VIEW v_players AS
SELECT players.*, MAX(scores.score) AS 'high_score'
FROM players LEFT JOIN scores ON players.id = scores.id_player
GROUP BY players.id
The advantage of using a VIEW
is not to update the table after each change of the scores
table.
How to use the UPDATE
automatically using a TRIGGER
:
The UPDATE
query can be used on a TRIGGER
to UPDATE
the table after INSERT
, UPDATE
or DELETE
of the scores
table, automatically:
DELIMITER //
-- trigger on UPDATE of table scores.
CREATE TRIGGER upd_players AFTER UPDATE ON scores
FOR EACH ROW
BEGIN
UPDATE `players` p SET p.high_score = (
SELECT MAX(score)
FROM scores
WHERE id_player = p.id AND id_player = NEW.id_player
);
END;//
-- trigger on INSERT of table scores.
CREATE TRIGGER ins_players AFTER INSERT ON scores
FOR EACH ROW
BEGIN
UPDATE `players` p SET p.high_score = (
SELECT MAX(score)
FROM scores
WHERE id_player = p.id AND id_player = NEW.id_player
);
END;//
-- trigger on DELETE of table scores.
CREATE TRIGGER del_players AFTER DELETE ON scores
FOR EACH ROW
BEGIN
UPDATE `players` p SET p.high_score = (
SELECT MAX(score)
FROM scores
WHERE id_player = p.id AND id_player = OLD.id_player
);
END;//
DELIMITER ;