updating records from another table with filtering

Question

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

This could sound stupid, but I would like to update the high score record of the first table, based on the maximum value on the second table.

I arrived to this solution, which is incomplete:

UPDATE `productos_players` p INNER JOIN `scores` s ON (p.id = s.id_player) SET p.high_score=s.score

This is what I've done so far, but I still need to modify the query for

scores

to get the maximum MAX() and limit it to 1 result or something similar, but I don't know how to do it, or even if it is possible.

Thanks for your help!!

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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
);

demo: http://sqlfiddle.com/#!9/7320c3/2/0

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 ;

updating records from another table with filtering

Sign up