MySQL 8.0

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

CREATE TABLE test_tbl  (
id int ,
regid int,
docid int ,
archived int 
);

INSERT INTO test_tbl  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 test_tbl;


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


update test_tbl t1 
set t1.archived = 1 
where t1.archived = 0 and t1.id not in ( select t2.id from (select max(id) as id,
                                                                   regid,
                                                                   max(docid)
                                                            from test_tbl 
                                                            group by regid
                                                           ) as t2 
                                       ) ;
select * from test_tbl;


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