Carles Carles - 9 months ago 79
MySQL Question

updating records from another table with filtering

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

Answer Source

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 ;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download