johnjohn22 johnjohn22 - 2 years ago 70
MySQL Question

Update column with previous record based on date

My table looks like this:

id_sensor | date_in | value | cons

I have a (almost) working Query:

UPDATE ow_data D,ow_data X
SET D.cons = X.value
WHERE D.id_sensor=14 AND X.id_sensor=D.id_sensor AND X.date_in>D.date_in

This works so that cons receives the value from the next record, since the records come in ascending date_in-order.

But what I want is to update cons with the value of the previous record instead.

But if I change to X.date_in < D.date_in instead, cons always receives the value from the first record, obviously because it comes first in X

I tried ORDER BY on X but it is not allowed it seems.


Turned out I solved it myself:

UPDATE ow_data D
WHERE id_sensor=14 GROUP BY date_in ORDER BY date_in DESC) X
ON D.date_in > X.date_in
SET D.cons = X.value WHERE D.id_sensor=14

Answer Source

This is a shot in the dark here because I've never had a situation where I had to do this, but can you do it this way?

UPDATE ow_data D 
SET D.cons = (select X.value from ow_data X where X.date_in < D.date_in and X.id_sensor = 14 order by X.date_in desc limit 1) 
WHERE D.id_sensor=14;

My example removes your X.id_sensor = D.id_sensor condition because if the id is known, you may as well just check against that.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download