Mysql update column with value from previous row

Question

I have a table in MySQL, like:

SELECT time_stamp, price,prev_price, FROM prices ORDER by time_stamp ASC;

-------------------------------
time_stamp | price | prev_price
-------------------------------
1382457241 |  23.3 | NULL
1393553097 |  14.2 | NULL
1412857868 |  16.0 | NULL
1412857965 | 201.1 | NULL
........

I want an UPDATE statement to update the prev_price with the value of the price from previous row (ordered by timestamp):

-------------------------------
time_stamp | price | prev_price
-------------------------------
1382457241 |  23.3 | NULL
1393553097 |  14.2 | 23.3
1412857868 |  16.0 | 14.2
1412857965 | 201.1 | 16.0
........

MySQL version: 5.7.24:

mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.7.24-0ubuntu0.16.04.1 |
+-------------------------+

Possible duplicate of [Simulate lag function in MySQL](https://stackoverflow.com/questions/11303532/simulate-lag-function-in-mysql) this post contains a answer for MySQL 5.1+ (MySQL user variables) and MySQL 8.0 (with a windows function) — Raymond Nijland, Dec 07 '18 at 13:54
@elena if my answer solved your problem, click the big checkbox to accept it as the answer. Otherwise write how you solved. — Alberto Moro, Dec 19 '18 at 11:05

score 1 · Answer 1 · answered Dec 07 '18 at 15:03

1

try this:

SELECT time_stamp,
       price,
       (SELECT e2.price
        FROM example e2
        WHERE e2.time_stamp < e1.time_stamp
        ORDER BY e2.time_stamp DESC
        LIMIT 1
       ) AS prev_price
FROM example e1
ORDER BY e1.time_stamp ASC

answered Dec 07 '18 at 15:03

Alberto Moro

1,014
11
22

Mysql update column with value from previous row

1 Answers1