MariaDB 10.5

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


10.5.13-MariaDB


CREATE TABLE `loop_msg` (
  `msg_id` int(11) NOT NULL,
  `content` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `loop_msg` (`msg_id`, `content`) VALUES
(2, 'Content 2'),
(3, 'Content 3'),
(4, 'Content 4'),
(6, 'Content 6'),
(7, 'Content 7'),
(8, 'Content 8');

CREATE TABLE `loop_msg_status` (
  `channel` bigint(20) NOT NULL,
  `msg_id` int(11) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `loop_msg_status` (`channel`, `msg_id`) VALUES
(316757642527768577, 4),
(384071823261696010, 6),
(939746456632438804, 8);


ALTER TABLE `loop_msg`
  ADD PRIMARY KEY (`msg_id`);

ALTER TABLE `loop_msg_status`
  ADD PRIMARY KEY (`channel`);


ALTER TABLE `loop_msg`
  MODIFY `msg_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;
COMMIT;
WITH cte AS (
        SELECT loop_msg.msg_id
             , channel
             , ROW_NUMBER() OVER w1 as rankx
             , COALESCE(
                           LEAD(loop_msg.msg_id)        OVER w1
                         , FIRST_VALUE(loop_msg.msg_id) OVER w1
                       ) AS msgid_next
             , COALESCE(
                           LEAD(content)        OVER w1
                         , FIRST_VALUE(content) OVER w1
                       ) AS content_next
          FROM      loop_msg
          LEFT JOIN loop_msg_status
            ON loop_msg.msg_id = loop_msg_status.msg_id
          WINDOW w1 AS (ORDER BY loop_msg.msg_id)
     )
SELECT channel, content_next, msg_id, msgid_next
  FROM cte
 WHERE channel IS NOT NULL
;


channel
content_next
msg_id
msgid_next


316757642527768577
Content 6
4
6

384071823261696010
Content 7
6
7

939746456632438804
Content 2
8
2


WITH cte AS (
        SELECT loop_msg.msg_id
             , channel
             , COALESCE(
                           LEAD(content)        OVER w1
                         , FIRST_VALUE(content) OVER w1
                       ) AS content_next
          FROM      loop_msg
          LEFT JOIN loop_msg_status
            ON loop_msg.msg_id = loop_msg_status.msg_id
          WINDOW w1 AS (ORDER BY loop_msg.msg_id)
     )
SELECT channel, content_next
  FROM cte
 WHERE channel IS NOT NULL
;


channel
content_next


316757642527768577
Content 6

384071823261696010
Content 7

939746456632438804
Content 2
channel	content_next	msg_id	msgid_next
316757642527768577	Content 6	4	6
384071823261696010	Content 7	6	7
939746456632438804	Content 2	8	2