how to update a column after updation of another column of same TABLE?

Question

I am trying to update a column with help of previously updated column in phpMyAdmin. I am trying to calculate ratio by dividing advance by dep (ratio=advance/dep). I have this table :

#id#   #dep#  #cash#  #advance#  #ratio#
----------------------------------------
1       100     100      200        0
----------------------------------------
2       200     300      500        0 
----------------------------------------

I am trying this code :

Update 'table1' Set 'ratio'= 'advance'/'dep';

I expect output to be 200/100 = 2 updated in ratio when i update value of columns dep or advance in table.

Hello, you can write trigger on table 'AFTER UPDATE ' to update required column. http://www.mysqltutorial.org/create-the-first-trigger-in-mysql.aspx — Sonam Tripathi, Apr 08 '19 at 07:21
You appear to be using single quotes rather than backticks in your update statement.https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-back-ticks-in-mysql — P.Salmon, Apr 08 '19 at 07:36
You wrote what is your expected output , but not what is the actual output — yunzen, Apr 08 '19 at 08:07

Sebastian Brosch · Answer 1 · 2019-04-08T08:04:38.677

You can use a CREATE TRIGGER on the table to calculate and set the new ratio of the affected rows:

CREATE TRIGGER upd_ratio BEFORE UPDATE ON table_name
FOR EACH ROW 
BEGIN
    SET NEW.ratio = NEW.advance / NEW.dep;
END

To UPDATE and recalculate all records you can use the following UPDATE statement. You should execute this once after creating the trigger to initialize the ratio column.

UPDATE `table_name` SET `ratio` = `advance` / `dep`

demo on dbfiddle.uk

You can also use a CREATE VIEW to create a view of the table (without the ratio column). You can dynamically calculate the ratio column and don't need to store and recalculate the value after UPDATE:

CREATE VIEW view_name AS SELECT *, advance / dep AS ratio FROM table_name;

demo on dbfiddle.uk

Note: As @P.Salmon also mentioned in the comments you need to use backticks instead of single-quotes. You current UPDATE statement isn't valid.

how to update a column after updation of another column of same TABLE?

1 Answers1