By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE your_table (
UNIQUE_ID INT,
LOCAL_ID INT,
GROUP_ID INT,
LOCAL_STAY_ID INT,
ORDER_IN_LOCAL_ID INT
);
INSERT INTO your_table (UNIQUE_ID, LOCAL_ID, GROUP_ID, LOCAL_STAY_ID, ORDER_IN_LOCAL_ID) VALUES
(1, 808, 1, 808, 1),
(2, NULL, 1, 909, 2),
(3, 676, 7, 676, 1),
(4, 674, 8, 674, 1),
(5, 352, 9, 352, 1),
(6, NULL, 9, 134, 2),
(7, 232, 11, 232, 1),
(8, NULL, 11, 431, 2),
(9, NULL, 11, 323, 3),
(10, NULL, 11, 567, 4),
(11, 800, 98, 800, 1),
(11, NULL, 98, 786, 1),
(11, NULL, 98, 345, 1);
Records: 13 Duplicates: 0 Warnings: 0
CREATE TEMPORARY TABLE temp_table AS
WITH RECURSIVE fill_nulls AS (
SELECT
UNIQUE_ID,
CASE WHEN ORDER_IN_LOCAL_ID = 1 THEN LOCAL_ID ELSE NULL END AS LOCAL_ID
FROM your_table
WHERE ORDER_IN_LOCAL_ID = 1
UNION ALL
SELECT
t.UNIQUE_ID,
CASE WHEN t.ORDER_IN_LOCAL_ID = 1 THEN t.LOCAL_ID ELSE fn.LOCAL_ID END
FROM your_table t
JOIN fill_nulls fn ON t.UNIQUE_ID = fn.UNIQUE_ID + 1
)
SELECT * FROM fill_nulls;
Records: 53 Duplicates: 0 Warnings: 0
UPDATE your_table AS orig
JOIN temp_table AS temp
ON orig.UNIQUE_ID = temp.UNIQUE_ID
SET orig.LOCAL_ID = temp.LOCAL_ID
WHERE orig.LOCAL_ID IS NULL;
Rows matched: 7 Changed: 7 Warnings: 0
SELECT * FROM your_table
| UNIQUE_ID | LOCAL_ID | GROUP_ID | LOCAL_STAY_ID | ORDER_IN_LOCAL_ID |
|---|---|---|---|---|
| 1 | 808 | 1 | 808 | 1 |
| 2 | 808 | 1 | 909 | 2 |
| 3 | 676 | 7 | 676 | 1 |
| 4 | 674 | 8 | 674 | 1 |
| 5 | 352 | 9 | 352 | 1 |
| 6 | 352 | 9 | 134 | 2 |
| 7 | 232 | 11 | 232 | 1 |
| 8 | 232 | 11 | 431 | 2 |
| 9 | 232 | 11 | 323 | 3 |
| 10 | 232 | 11 | 567 | 4 |
| 11 | 800 | 98 | 800 | 1 |
| 11 | 800 | 98 | 786 | 1 |
| 11 | 800 | 98 | 345 | 1 |