如何使用Trigger将列值更新为旧值加上来自其他表的新值？

How can I Update column value to Old value plus New value from other table using Trigger if that value has already have an entry? What I wanted is something like the following. Notice the bold and italicized part.

如果该值已经有一个条目,如何使用Trigger将列值更新为旧值加上来自其他表的新值?我想要的是以下内容。注意粗体和斜体部分。

DELIMITER$$
CREATE TRIGGER trigger_name AFTER INSERT
ON table_one FOR EACH ROW
BEGIN
  INSERT INTO table_two(clmn_id, clmn_one) VALUES(NEW.clmn_id_fk,NEW.clmn_a)
  ON DUPLICATE KEY UPDATE clmn_one = VALUES(clmn_one + NEW.clmn_a);
END$$
DELIMITER;

2 个解决方案

#1

Try removing the keyword VALUES from the ON DUPLICATE KEY:

尝试从ON DUPLICATE KEY中删除关键字VALUES:

DELIMITER$$
CREATE TRIGGER trigger_name AFTER INSERT
ON table_one FOR EACH ROW
BEGIN
  INSERT INTO table_two(clmn_id, clmn_one) VALUES(NEW.clmn_id_fk,NEW.clmn_a)
  ON DUPLICATE KEY UPDATE fine_amount = clmn_one + NEW.clmn_a;
END$$
DELIMITER;

#2

Looks like you need a select statement first, to check if it already exists. If so, set variables to current values, then run an update that combines the old values (variables) and new values. If the record doesn't already exist, run insert statement with current values.

看起来你首先需要一个select语句来检查它是否已经存在。如果是,请将变量设置为当前值,然后运行组合旧值(变量)和新值的更新。如果记录尚不存在,请使用当前值运行insert语句。