Question 20470 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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;
Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search