Rutime calculation in mysql -

i hope fine , learning more. need advice on update statement populate 1 column on run table. using mysql 5.6.25-. please find below table , data scripts.

create table test (symbol varchar(2), sym_date date, amount int, diff_amt int primary key (symbol,sym_date)) engine=innodb;  insert test(symbol,sym_date, amount) values('a','2015-07-01',200); insert test(symbol,sym_date, amount) values('a','2015-07-02',100); insert test(symbol,sym_date, amount) values('a','2015-07-03',500); insert test(symbol,sym_date, amount) values('a','2015-07-04',800); insert test(symbol,sym_date, amount) values('b','2015-07-03',300); insert test(symbol,sym_date, amount) values('b','2015-07-05',500); insert test(symbol,sym_date, amount) values('b','2015-07-06',600); insert test(symbol,sym_date, amount) values('c','2015-07-09',100); insert test(symbol,sym_date, amount) values('c','2015-07-11',100); insert test(symbol,sym_date, amount) values('c','2015-07-12',100); 

i need difference amount column between result set based on symbol while symbol, sym_date in ascending order in remaining column diff_amt.

select symbol, sym_date, amount test order symbol, sym_date;  give me result set.  symbol   sym_date      amount ----------------------------       2015-07-01      200       2015-07-02      100       2015-07-03      500       2015-07-04      800 b       2015-07-03      300 b       2015-07-05      500 b       2015-07-06      600 c       2015-07-09      100 c       2015-07-11      100 c       2015-07-12      100 

i need diff_amt column populated difference in result set. each symbol, first value same. next row 2nd value - 1st value, give result r1. next row 3rd value - r1 , contiue same.

expected result set below:  symbol   sym_date     amount   diff_amt -------------------------------------------------       2015-07-01      200     200         -- (ar1 = a1)       2015-07-02      100     -100        -- (ar2 = a2 - ar1)       2015-07-03      500     600         -- (ar3 = a3 - ar2)       2015-07-04      800     200         -- (ar4 = a4 - ar3) b       2015-07-03      300     300         -- (br1 = b1) b       2015-07-05      500     200         -- (br2 = b2 - br1) b       2015-07-06      600     400         -- (br3 = b3 - br2) c       2015-07-09      100     100         -- (cr1 = c1) c       2015-07-11      100     0           -- (cr2 = c2 - cr1) c       2015-07-12      100     100         -- (cr3 = c3 - cr2) 

this sample scenario. looking general solution apply big table.

please let me know if more information needed.

all replies or suggestions highly appreciated.

thank in advance.

you can using variables. think following logic:

select t.*,        (@val := if(@s = symbol, amount - @val,                    if(@s := symbol, amount, amount)                   )        ) diff_amount test t cross join      (select @s := '', @val := 0) params order symbol, sym_date;