how to change Mysql variable inside query?

Question

I have table with two columns namely cumulative_sum and absolute. But I only have cumulative value. I would like to calculate absolute value.

Cumulative Value
12
19
32
41

Expected Absolute Value
12
7
13
9

I have try a query like this. I just need to update @absvalue to cum every time query update.

set @absvalue := 0;
update pdb_tint_result set abs = (cum - @absvalue)
where user_id='P6'
order by date;

Can you please guide me?

I saw calculating cumulative sum here I try to make it work for calculation Abs value.

score 2 · Answer 1 · edited Jul 01 '15 at 17:20

2

Try this one -

SET @prev_cum:=0;
UPDATE test_point_sum 
   SET absolute_value = (cumulative_sum - @prev_cum),
       cumulative_sum = @prev_cum:=cumulative_sum 
   ORDER BY 
      ID ASC;

Pick your table and column names. tested and verified. see if this helps

edited Jul 01 '15 at 17:20

DRapp

47,638
12
72
142

answered Jul 01 '15 at 04:07

stack_d_code

1,196
2
12
19

it got right only for the first two rows. other value is wrong. – Soe Chit Kaung Jul 01 '15 at 04:22
Edited my post now; earlier It was updating for iterative absolute_value. nevertheless, updated query would help. – stack_d_code Jul 01 '15 at 04:55

score 1 · Accepted Answer · answered Jul 01 '15 at 03:48

1

If you want to do something automatically with a changing variable I will recommend using triggers. To use triggers in your case

"CREATE TRIGGER update_abs 
 BEFORE UPDATE ON pdb_tint_result
 FOR EACH ROW SET abs = cum - New.absvalue;"

I hope it helped.

answered Jul 01 '15 at 03:48

Gideon Appoh

678
1
6
15

Can u please explain more? how do i use this – Soe Chit Kaung Jul 01 '15 at 04:04
@Chit, when you create a trigger, its something like an automated action which will always happen `BEFORE` or `AFTER` an event. In your case you need it to happen `BEFORE` the `UPDATE` event. So using the trigger above will always subtract the `new absvalue` from the `cum` before updating the `abs`. – Gideon Appoh Jul 01 '15 at 04:10
Mind you, triggers are not the SQL, so after specifying the trigger you then run your sql's – Gideon Appoh Jul 01 '15 at 04:11
Ok. Thanks a lot. I will try – Soe Chit Kaung Jul 01 '15 at 04:22
@Soe, here the best way to say thank you is to mark your question as solved by checking the answer which helped you most – Gideon Appoh Jul 01 '15 at 04:23

how to change Mysql variable inside query?

2 Answers2