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.

DRapp · Accepted Answer · 2015-07-01 17:20:50Z

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 1, 2015 at 17:20

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

answered Jul 1, 2015 at 4:07

stack_d_code

1,2763 gold badges13 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Soe Chit Kaung Over a year ago

it got right only for the first two rows. other value is wrong.

stack_d_code Over a year ago

Edited my post now; earlier It was updating for iterative absolute_value. nevertheless, updated query would help.

Gideon Appoh · Accepted Answer · 2015-07-01 03:48:56Z

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 1, 2015 at 3:48

Gideon Appoh

6631 gold badge6 silver badges15 bronze badges

4 Comments

Soe Chit Kaung Over a year ago

Can u please explain more? how do i use this

Gideon Appoh Over a year ago

@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 Over a year ago

Mind you, triggers are not the SQL, so after specifying the trigger you then run your sql's

Gideon Appoh Over a year ago

@Soe, here the best way to say thank you is to mark your question as solved by checking the answer which helped you most

Collectives™ on Stack Overflow

how to change Mysql variable inside query?

2 Answers 2

2 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related