Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – update column value when other columns change

I have a table with 3 columns (value1, value2 and total).

When you update the value1 or value2 column, I need the sum of the two to be updated in the total column.

I know there’s a way to trigger it, but since it does it after the update and updates a column, won’t it stay in a loop?

Tags:

2

Answers


  1. 0

    you’ll need to check if the update affects to value1 or value2 to trigger it:

    CREATE TRIGGER update_total_after_update
AFTER UPDATE ON your_table
FOR EACH ROW
BEGIN
    IF NEW.value1 != OLD.value1 OR NEW.value2 != OLD.value2 THEN
        SET NEW.total = NEW.value1 + NEW.value2;
    END IF;
END;
    Login or Signup to reply.
  2. 0

    You may use generated column:

    CREATE TABLE test (
  id INT AUTO_INCREMENT PRIMARY KEY,
  value1 INT,
  value2 INT,
  total INT AS (value1 + value2) 
);
INSERT INTO test (value1, value2) VALUES (1,2), (3,4);
INSERT INTO test VALUES (DEFAULT,5,6,DEFAULT), (DEFAULT,7,8,DEFAULT);
SELECT * FROM test;
    id value1 value2 total
    1 1 2 3
    2 3 4 7
    3 5 6 11
    4 7 8 15

    fiddle

    Remember – you cannot set (in INSERT) or update (in UPDATE, INSERT ODKU, MERGE, etc.) the value in generated column, you can only retrieve its value.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search