MySQL根据前一行(同一列)更新列 [英] MySQL update column based on previous row (same column)

问题描述

我有以下数据:

ID | Image
1  |  10
2  |  11
3  |  
4  |
5  |

我想用前一行的值加一来更新缺失值.

And I would like to update the missing values with the value of the row before plus one.

最终输出应该是:

ID | Image
1  |  10
2  |  11
3  |  12
4  |  13
5  |  14

我在更新过程中想到了一个选择，但它不起作用.

I thought about a select during the update, but it doesn't work.

UPDATE items AS item1
SET item1.image = (SELECT image 
                   FROM items AS item2 
                   WHERE item2.id < item1.id 
                   ORDER BY item2.id DESC LIMIT 1) + 1

推荐答案

您可以使用 UPDATE 和 JOIN 到派生表:

You can use an UPDATE with a JOIN to a derived table for this:

UPDATE Items AS i1
JOIN (
  SELECT ID, @n := @n + 1 AS Image
  FROM Items
  CROSS JOIN (SELECT @n := (SELECT MAX(Image) FROM Items)) AS v
  WHERE Image IS NULL
  ORDER BY ID
) AS i2 ON i1.ID = i2.ID
SET i1.Image = i2.Image;

派生表使用变量来计算具有 NULL 的记录的 Image 值.

The derived table uses variables in order to calculate the Image values of the records having NULLs.

此处演示

这篇关于MySQL根据前一行(同一列)更新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！