MySQL 根据前一行(同一列)更新列

Question

我有以下数据:

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

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

最终输出应该是:

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

更新的时候想过select，但是不行。

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

Answer 1

为此，您可以将 UPDATE 与 JOIN 结合使用到派生表:

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 值。

Demo here