0

I do not understand how to update a table from another table after a table has been modified.

I have a table with 3 columns like id(INT), distname(VARCHAR)UNIQUE and score(INT), and I have another table with id(INT), name(VARCHAR), points(INT), penalizes(INT)

each time a name gets some points they go to the second table.

I want to write a routine that makes the following

SELECT DISTINCT(name) AS distname,
       SELECT(SUM(points)-SUM(penalizes)) AS score
    FROM table2 

and updates the table1 each time before someone makes a select on table1, or each time someone enters data into table2

How would this look like?

1

The update command for Table 1 to update all names score

Would look like

UPDATE table1 as t1 
INNER JOIN (SELECT DISTINCT(name) AS distname, SUM(points)-SUM(penalizes) AS score 
  FROM table2
  GROUP BY name) t2
ON t1.distname = t2.distname SET t1.score = t2.score;

But better would be a trigger, which update only the rows in table1, username is actualy updated or inserted

DELIMITER $$

CREATE TRIGGER after_table2_Insert
    AFTER INSERT
    ON tabel2 FOR EACH ROW
BEGIN
    UPDATE table1 as t1 
    INNER JOIN (SELECT DISTINCT(name) AS distname, SUM(points)-SUM(penalizes) AS score 
      FROM table2
      Where name = NEW.name
      GROUP BY name ) t2
    ON t1.distname = t2.distname SET t1.score = t2.score;

END$$    

DELIMITER ;

And something similar for updating table2

DELIMITER $$

CREATE TRIGGER after_table_update
AFTER UPDATE
ON table FOR EACH ROW
BEGIN
        UPDATE table1 as t1 
        INNER JOIN (SELECT DISTINCT(name) AS distname, SUM(points)-SUM(penalizes) AS score 
          FROM table2
          Where name = NEW.name
          GROUP BY name ) t2
        ON t1.distname = t2.distname SET t1.score = t2.score;
END$$

DELIMITER ;
|improve this answer|||||

Not the answer you're looking for? Browse other questions tagged or ask your own question.