MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE test (id INT PRIMARY KEY, value INT);
INSERT INTO test VALUES
(1,123),
(2,NULL),
(3,NULL),
(4,456),
(5,NULL),
(6,NULL),
(7,789),
(8,NULL),
(9,NULL);
SELECT * FROM test;


id
value


1
123

2
null

3
null

4
456

5
null

6
null

7
789

8
null

9
null


UPDATE test t0
JOIN ( SELECT t1.id, MAX(t2.id) t2id
       FROM test t1
       JOIN test t2 ON t1.id > t2.id
       WHERE t1.value IS NULL
         AND t2.value IS NOT NULL
       GROUP BY t1.id ) t3 ON t0.id = t3.id
JOIN test t4 ON t3.t2id = t4.id
SET t0.value = t4.value
SELECT * FROM test;


id
value


1
123

2
123

3
123

4
456

5
456

6
456

7
789

8
789

9
789

id	value
1	123
2	null
3	null
4	456
5	null
6	null
7	789
8	null
9	null