MySQL update column based on previous row (same column)

Question

I have the following data:

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.

The final output should be:

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

Answer 1

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;

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

Demo here

Answer 2

Try this solution using a user variable, with a complete demo as below.

SQL:

-- data
create table items(ID int, Image int);
insert into items values
(1, 10),(2, NULL),(3, NULL),(4, NULL),(5, NULL);
SELECT * FROM items;

-- SQL needed
SET @i = 0;
UPDATE items
SET Image = (IF(Image IS NULL OR Image = '',
                @i:=@i+1,
                @i:=Image
                ));
SELECT * FROM items;

Output:

mysql> SELECT * FROM items;
+------+-------+
| ID   | Image |
+------+-------+
|    1 |    10 |
|    2 |  NULL |
|    3 |  NULL |
|    4 |  NULL |
|    5 |  NULL |
+------+-------+
5 rows in set (0.00 sec)

mysql>
mysql> SET @i = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE items
    -> SET Image = (IF(Image IS NULL OR Image = '',
    ->                 @i:=@i+1,
    ->                 @i:=Image
    ->                 ));
Query OK, 4 rows affected (0.00 sec)
Rows matched: 5  Changed: 4  Warnings: 0

mysql> SELECT * FROM items;
+------+-------+
| ID   | Image |
+------+-------+
|    1 |    10 |
|    2 |    11 |
|    3 |    12 |
|    4 |    13 |
|    5 |    14 |
+------+-------+
5 rows in set (0.00 sec)