MySQL 根据前一行（同一列）更新列

我有以下数据：

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

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

最终输出应该是：

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

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

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 with a 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;

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

演示在这里 http://sqlfiddle.com/#!9/e156f/1