How to Update Subtract value to the existing Table Using Mysql

user_5350089 0
Viewed 87

I have three columns in my table the name of the column is date_time,total,sub and i want to update the existing sub column by subtracting previous row total value and next row total value by order of date_time column using mysql.I don't know how to achieve this result.The data are given below

   date_time           total     sub

2019-03-07 12:32:10     50        0
2019-03-07 12:34:22     60        0
2019-03-07 12:37:17     75        0
2019-03-07 12:44:10     100       0

i want to update the above record as

  date_time           total     sub

2019-03-07 12:32:10     50        0
2019-03-07 12:34:22     60        10
2019-03-07 12:37:17     75        15
2019-03-07 12:44:10     100       25

Please guide me to achieve this result

mysql
1 Answers

Try this code

update your_table_name U left join 
(select 
      (CASE
         WHEN @pev_row_value = 0 THEN  0   
         ELSE (`your_table_name `.total - @pev_row_value) 
       END) as sub_result,  
       (@pev_row_value :=  `your_table_name `.total ) as current_row,
       users.* 
       FROM `your_table_name ` cross join
       (select @pev_row_value := 0) params  
 order by date_time asc) A on U.date_time=A.date_time 
 set U.sub = A.sub_result

Hopefully it will be work fine. In CASE WHEN ELSE statement set the sub_result for update and pev_row_value stored the value of previous row of total column value. Then whole regenerated value worked as a table which new name is A and update the final value with join the new table A.

Thanks

user_57465920
Related