MySQL触发器基于另一个表中列的总和来更新字段 [英] MySQL triggers to update field based on sum of column from another table

问题描述

我有两个表调查"和结果"

I have two tables 'survey' and 'results'

survey table
-----------------------------------------------
survey_id  | name  | p1q1 | p1q2 | p1q5  
-----------------------------------------------
1          | John  | 10   | 10   | 5 
-----------------------------------------------
2          | Erick | 12   | 15   | 23 
-----------------------------------------------

results table

id  | Uptake | PY5Q1 |
----------------------------
1   | AZT    | NULL  |
----------------------------
2   | UPDP   | NULL  |
----------------------------

我是触发器的新手，正在尝试弄清楚如何在调查表中获取p1q5的总和并将其更新为结果表中id = 1的PY5Q1 任何想法将不胜感激

I'm new to triggers and am trying to figure out how to get sum of p1q5 in survey table and update it to PY5Q1 where id = 1 in results table Any ideas will be appreciated

CREATE TRIGGER `results` AFTER INSERT ON `survey` FOR EACH ROW BEGIN
UPDATE results SET PY5Q1=(SELECT SUM(p1q5)) WHERE id=1;
END

推荐答案

CREATE TRIGGER `results` AFTER INSERT ON `survey` FOR EACH ROW
BEGIN
UPDATE results SET PY5Q1=(SELECT SUM(p1q5)FROM survey) WHERE id=1;
END

这篇关于MySQL触发器基于另一个表中列的总和来更新字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！