CODEWITHSUNDEEP
mysqlsqltriggerssql-update
Michael
Michael

Reputation: 123

How can I Update column value to Old value plus New value from other table using 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.

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;

Upvotes: 2

Views: 4133

Answers (2)

Filipe Silva
Filipe Silva

Reputation: 21657

Try removing the keyword VALUES from the ON DUPLICATE KEY:

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;

Upvotes: 3

Seano666
Seano666

Reputation: 2238

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.

Upvotes: 0

Related Questions