MySQL 5.7

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

CREATE TABLE test (logid INT, settingid INT, status VARCHAR(255), mtime	DATE);
INSERT INTO test VALUES
(1, 	1,	NULL	,	'2022-10-01'),
(2, 	1,	'ACTIVE',	'2022-10-02'),
(3, 	1,	NULL	,	'2022-10-03'),
(4, 	1,	NULL	,	'2022-10-04'),
(5, 	1,	'INACTIVE',	'2022-10-05'),
(6, 	1,	'ACTIVE',	'2022-10-06'),
(7, 	1,	'INACTIVE',	'2022-10-07'),
(8, 	1,	NULL	,	'2022-10-07'),
(9, 	1,	NULL	,	'2022-10-09'),
(10,	2,	'ACTIVE',	'2022-10-10'),
(11,	2,	NULL	,	'2022-10-11');
SELECT * FROM test ORDER BY settingid, mtime;

Records: 11  Duplicates: 0  Warnings: 0




logid
settingid
status
mtime


1
1
null
2022-10-01

2
1
ACTIVE
2022-10-02

3
1
null
2022-10-03

4
1
null
2022-10-04

5
1
INACTIVE
2022-10-05

6
1
ACTIVE
2022-10-06

7
1
INACTIVE
2022-10-07

8
1
null
2022-10-07

9
1
null
2022-10-09

10
2
ACTIVE
2022-10-10

11
2
null
2022-10-11


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;

Rows matched: 11  Changed: 6  Warnings: 0


SELECT * FROM test ORDER BY settingid, mtime;


logid
settingid
status
mtime


1
1
INACTIVE
2022-10-01

2
1
ACTIVE
2022-10-02

3
1
ACTIVE
2022-10-03

4
1
ACTIVE
2022-10-04

5
1
INACTIVE
2022-10-05

6
1
ACTIVE
2022-10-06

7
1
INACTIVE
2022-10-07

8
1
INACTIVE
2022-10-07

9
1
INACTIVE
2022-10-09

10
2
ACTIVE
2022-10-10

11
2
ACTIVE
2022-10-11

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