Update missing values in MySQL 5.6 table by selecting values from closest records with same id

Question

Let's say I have a table with logs. The value of status column may be NULL and I would like to UPDATE this value by copying value from the log for same settingid which have non-NULL status and closest (smaller or equal) mtime.

logid	settingid	status	mtime
1	1	NULL	2022-10-01	# NULL -> INACTIVE default
2	1	ACTIVE	2022-10-02
3	1	NULL	2022-10-03	# ACTIVE from 2
4	1	NULL	2022-10-04	# ACTIVE from 2
5	1	INACTIVE	2022-10-05
6	1	ACTIVE	2022-10-06
7	1	INACTIVE	2022-10-07
8	1	NULL	2022-10-07	# INACTIVE from 7
9	1	NULL	2022-10-09	# INACTIVE from 7
10	2	ACTIVE	2022-10-10
11	2	NULL	2022-10-11	# ACTIVE from 10

I have UPDATE query that works in MySQL 8 but how can I make it work in MySQL 5.6?

 WITH cte AS (
     SELECT
           logid,
           status,
           mtime,
           CASE WHEN status IS NULL THEN
               IFNULL(
                   (
                       SELECT status
                       FROM logs
                       WHERE
                           settingid = t.settingid
                           AND status IS NOT NULL
                           AND mtime <= t.mtime
                           ORDER BY mtime DESC
                           LIMIT 1
                   ),
                   'INACTIVE'
               ) ELSE status END AS new_status
       FROM
           logs t
       ORDER BY mtime
   )
   UPDATE logs t LEFT JOIN cte USING (logid)
   SET t.status = cte.new_status
   WHERE t.status IS NULL;

@Akina There is really no benefit in changing to a markdown format table, because the table should be given as initialization code (aligned in columns) for a [mre] and not given as an ascii or markdown format table. The edit just promotes misconceptions & bad posts for the poster. — philipxy, Mar 13 '23 at 08:35

score 0 · Answer 1 · answered Mar 13 '23 at 07:28

you can use a nested subquery to select the closest record with a non-NULL status

UPDATE logs t
SET t.status = (
    SELECT IFNULL(MAX(l.status), 'INACTIVE')
    FROM logs l
    WHERE l.settingid = t.settingid
    AND l.status IS NOT NULL
    AND l.mtime <= t.mtime
)
WHERE t.status IS NULL;

score 0 · Answer 2 · answered Mar 13 '23 at 08:25

how can I make it work in MySQL 5.6?

Use user-defined variables.

UPDATE test
SET status = CASE WHEN status IS NOT NULL      THEN (@status := status)
                  WHEN settingid <> @settingid THEN (@status := 'INACTIVE')
                  ELSE                              @status
                  END,
    settingid = (@settingid := settingid)
WHERE (@status := '') IS NOT NULL
  AND (@settingid := 0) IS NOT NULL
ORDER BY settingid, mtime;

https://dbfiddle.uk/FaKFbYSJ

PS. This works in 8.0 too producing a lot of warning 1287.

Update missing values in MySQL 5.6 table by selecting values from closest records with same id

2 Answers2