php - Update values of database with values that are already in DB

Question

I've a database that stores data read from different sensors. The table looks like this:

[SensorID][timestampMS][value]
[Sensor1][123420][10]
[Sensor1][123424][15]
[Sensor1][123428][6554]
[Sensor1][123429][20]

What I would like to do is the following: There are some reads that are corrupted (numbers that are 6554), and I would like to Update that with the next value that is not corrupted (in the example shown below that would be 20). So, if a number is 6554, I would like to update that with the next value (in timestamp), that is not corrupted.
I was thinking on doing this in PHP, but I wonder if it's possible to do it directly with a SQL script.

Appreciate :)

Answer 1

You can use a correlated sub-query...

UPDATE
  myTable
SET
  value = (SELECT value FROM myTable AS NextValue WHERE sensorID = myTable.SensorID AND timestampMS > myTable.timestampMS ORDER BY timestampMS ASC LIMIT 1)
WHERE
  value = 6554

The sub-query gets all the following results, ordered by timestampMS and takes just the first one; That being the next value for that SensorID.

Note: If no "next" value exists, it will attempt to update with a value of NULL. To get around this, you can add this to the WHERE clause...

  AND EXISTS (SELECT value FROM myTable AS NextValue WHERE sensorID = myTable.SensorID AND timestampMS > myTable.timestampMS ORDER BY timestampMS ASC LIMIT 1)

EDIT

Or, to be shorter, just use IFNULL(<sub_query>, value)...