MySQL 8.0

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

select version();


version()


8.0.27


CREATE TABLE t1(ID INTEGER PRIMARY KEY, regid integer, docid integer, archived integer);

INSERT INTO t1 VALUES
   (1,1000,1,0),
   (2,1000,2,0),
   (3,1000,3,0),
   (4,2000,1,0),
   (5,2000,2,0),
   (6,3000,1,0),
   (7,3000,2,0),
   (8,3000,3,0),
   (9,3000,4,0);
SELECT * FROM t1;


ID
regid
docid
archived


1
1000
1
0

2
1000
2
0

3
1000
3
0

4
2000
1
0

5
2000
2
0

6
3000
1
0

7
3000
2
0

8
3000
3
0

9
3000
4
0


-- Select the MAX, for every docid
SELECT
  ID,
  regid,
  docid,
  (SELECT MAX(docid) FROM t1 te where te.regid=t.regid) as M
FROM t1 t


ID
regid
docid
M


1
1000
1
3

2
1000
2
3

3
1000
3
3

4
2000
1
2

5
2000
2
2

6
3000
1
4

7
3000
2
4

8
3000
3
4

9
3000
4
4


UPDATE t1
JOIN (

  SELECT
    ID,
    regid,
    docid,
    (SELECT MAX(docid) FROM t1 te where te.regid=t.regid) as M
  FROM t1 t

) x ON t1.id=x.id 
SET archived = 1
WHERE t1.docid<x.M;
SELECT * FROM t1;


ID
regid
docid
archived


1
1000
1
1

2
1000
2
1

3
1000
3
0

4
2000
1
1

5
2000
2
0

6
3000
1
1

7
3000
2
1

8
3000
3
1

9
3000
4
0

ID	regid	docid
1	1000	1
2	1000	2
3	1000	3
4	2000	1
5	2000	2
6	3000	1
7	3000	2
8	3000	3
9	3000	4