SQL Update the field that depend on last record in same table

Question

I have table like this.

existing condition
+----+------+-----+
| x  |   a  |  b  |
+----+------+-----+
| 1  |   3  |  0  |
| 3  |   6  |  0  |
| 7  |   2  |  0  |
| 12 |  -1  |  0  |
| 16 |   8  |  0  |
| 23 |  -6  |  0  |
| 28 |   4  |  0  |
+----+------+-----+

Column x is a Date data type, it must ordered by date.
My question is, i want column b have the value that satisfy

b = a + b'

where b' is the value before the updated record

expected condition
+----+------+------+
| x  |   a  |   b  |
+----+------+------+
| 1  |   3  |   3  |
| 3  |   6  |   9  |
| 7  |   2  |  11  |
| 12 |  -1  |  10  |
| 16 |   8  |  18  |
| 23 |  -6  |  12  |
| 28 |   4  |  16  |
+----+------+------+

for x=1, b=3 because it is the first data
for x=3, b=9 because a=6 and last b=3 so (6 + 3)
...
for x=16, b=18 because a=8 and last b=10 so (8 + 10)
...
and so on.

How do i update the column b with single update statement?
Is that possible?

oh you want a RUNNING TOTAL for column B.... like this prior answer? http://stackoverflow.com/questions/664700/calculate-a-running-total-in-mysql — xQbert, Aug 19 '15 at 18:58
yes. i don't know what the name for this case. Thanks for pointing the url. — Mirza, Aug 19 '15 at 19:19
Also see this question for useful information: http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause — Turophile, Aug 19 '15 at 23:48

score 1 · Answer 1 · answered Aug 19 '15 at 18:58

1

This is the basic version:

SET @foo := 0;
UPDATE yourtable SET a=a+@foo, @foo := b ORDER BY x

set a variable to 0, then update your a field with that variable's value, then update the variable to the b field's value. As mysql evaluates variable usage/assignment in left->right order, the a=a+@foo will use the PREVIOUS row's value for @foo.

answered Aug 19 '15 at 18:58

Marc B

356,200
43
426
500

1

@Marc..the OP also tagged `postgres`. `lead`,`lag` may also be helpful. +1 anyway. – Vamsi Prabhala Aug 19 '15 at 19:02
Thanks for answering @Marc B, this solution seems right, but i don't know why it has an error on my phpmyadmin. And I think your a and b value is swapped. – Mirza Aug 19 '15 at 19:25
yea, got the a/b reversed, but the basic logic holds. – Marc B Aug 19 '15 at 19:29
I think this is not true: *As mysql evaluates variable usage/assignment in left->right order*. In [MySQL manual](https://dev.mysql.com/doc/refman/5.0/en/user-variables.html) is stated that *the order of evaluation for expressions involving user variables is undefined*. – Giorgos Betsos Aug 19 '15 at 19:32
undefined, but still for the past 10ish years has done it left->right. – Marc B Aug 19 '15 at 19:33

score 0 · Answer 2 · answered Aug 19 '15 at 21:41

try:

update example3 target set b= acum
  from 
   (select x, a, b, sum(a) over (order by x) as acum  
      from example3) source
  where source.x=target.x;

It does what you expect.

Create schema:

create table example3(
  x integer primary key,
  a integer,
  b integer,
  expected integer
);

insert into example3 (x,a,expected) values
(1  , 3 ,  3  ),
(3  , 6 ,  9  ),
(7  , 2 , 11  ),
(12 ,-1 , 10  ),
(16 , 8 , 18  ),
(23 ,-6 , 12  ),
(28 , 4 , 16  );

score 0 · Answer 3 · answered Aug 19 '15 at 23:44

0

I would do it like this:

update mytable as t1 
set b = (select sum(a) as acum  from (select x, a from mytable) as t2 where t2.x <= t1.x) 
;

See fiddle here: http://sqlfiddle.com/#!9/7d624/1

Thanks to @Emilio for providing sample ddl & data.

answered Aug 19 '15 at 23:44

Turophile

3,367
1
13
21

Be aware that your function doesn't use windowing functions, it uses one subselect inside other subselect. This solution may have performance issues. – Emilio Platzer Aug 20 '15 at 13:12

SQL Update the field that depend on last record in same table

3 Answers3