Update values of database with values that are already in DB

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 :)

---

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)...

---

Not sure if this is valid syntax, can't test it ATM. You may need to change this to be JOINs instead of the nested subqueries, but in concept you can do something like (for SQL Server):

UPDATE t1
SET Value = (   SELECT Value 
                from MyTable t2
                WHERE t2.SensorID =t1.SensorID
                    AND t2.[timestamp] = 
                    (   SELECT MIN([TimeStamp]) 
                        FROM mytable t3 
                        where t3.sensorid = t2.sensorID 
                            AND t3.[timestamp] > t2.[timestamp]
                    )
            )       
FROM Mytable t1
WHERE t1.value = 6554

---

I did a workaround based on Dems solution, and it works in Mysql:

I've created a "copy" of the sensors table like this:

drop table if exists sensors_new;
create table if not exists sensors_new like sensors;
insert into sensors_new select * from sensors;

Then I do what Dems recommended me doing, but using this new aux table in the select (to avoid the error that Mysql launches when Updating a table while doing a select in the same table).

UPDATE
sensors
SET
raw_data = (SELECT raw_data FROM sensors_new AS NextValue WHERE sensor_id = sensors.sensor_id AND timestampMS > sensors.timestampMS ORDER BY timestampMS ASC LIMIT 1)
WHERE
value = 6554

Then, just drop this auxiliar table.

I hope this helps Mysql users.