從一個表中選擇並更新另一個表的常式

我不明白如何在修改表後從另一個表更新表。

我有一個包含 3 列的表，例如 id(INT)、distname(VARCHAR)UNIQUE 和 score(INT)，我還有另一個包含 id(INT)、name(VARCHAR)、points(INT)、penalizes(INT) 的表

每次一個名字獲得一些分數時，他們都會去第二張桌子。

我想編寫一個使以下內容的常式

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

並在每次有人對 table1 進行選擇之前或每次有人將數據輸入 table2 時更新 table1

這會是什麼樣子？

表 1 更新所有名稱分數的更新命令

看起來像

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;

但最好是一個觸發器，它只更新 table1 中的行，使用者名實際上是更新或插入的

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 ;

以及用於更新 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 ;

引用自：https://dba.stackexchange.com/questions/258660