使用数据库中已经存在的值更新数据库的值

问题描述

我有一个数据库，用于存储从不同传感器读取的数据。该表如下所示：

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

我想做的是：有些读取已损坏（数字为6554），我想更新下一个未损坏的值（在下面的示例中为20）。因此，如果数字为6554，我想使用下一个值（以时间戳记）更新该数字，即未损坏。


我正在考虑使用PHP进行此操作，但我想知道是否可以直接使用SQL脚本来完成此操作。

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.

欣赏：）

推荐答案

您可以使用相关的子查询...

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

-query得到以下所有结果，按timestampMS排序，仅取第一个；这是该SensorID的下一个值。

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.

注意：如果不存在下一个值，它将尝试使用NULL值进行更新。要解决此问题，可以将其添加到WHERE子句中。

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)

编辑

或者简而言之，只需使用 IFNULL（< sub_query> ;,值） ...

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

这篇关于使用数据库中已经存在的值更新数据库的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！