Update Value based on previous row in MySQL

Question

This question is based on my previous question given below. Get percentage based on value in previous row

Instead of getting a select statement, I want an update statement to fill the rum column using update query.

Input:

+---+--------+------+
|CID| number | rum  |
+---+--------+------+
| 1 | 1.0000 | NULL |
| 3 | 2.0000 | NULL |
| 5 | 2.0000 | NULL |
| 6 | 4.0000 | NULL |
+---+--------+------+

Output:

+---+--------+------+
|CID| number | rum  |
+---+--------+------+
| 1 | 1.0000 | NULL |
| 3 | 2.0000 |100.0 |
| 5 | 2.0000 |  0.0 |
| 6 | 4.0000 |100.0 |
+---+--------+------+

Formula:

rum = (currNumber - prevNumber) / prevNumber * 100

[Simulate lag function in MySQL](https://stackoverflow.com/a/49699931/5070879) - native support for LAG function starting from MySQL 8.0 — Lukasz Szozda, Dec 26 '19 at 13:21
Lag function doesn't work in MySQL. I need an update query. I do not have mastery on sql other than joins and group bys — Tomrock D'souza, Dec 26 '19 at 13:23
thanks the lag function worked it didn't work on my previous mariadb but now it does. — Tomrock D'souza, Dec 26 '19 at 13:34

score 0 · Accepted Answer · answered Dec 26 '19 at 13:23

You can use a JOIN and window functions in an UPDATE. One method is:

update t join
       (select t.*, lag(number) over (order by cid) as prev_number
        from t
       ) tt
       on tt.cid = t.cid
    set rum = (number - prev_number) * 100 / prev_number;

Update Value based on previous row in MySQL

1 Answers1

Linked