MySQL 8.0

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

create table a (subjectid int, groupid int, _groupid int);
create table b (groupid int, subjectid int);

insert into a select 5,null,1;
insert into a select 5,null,2;
insert into a select 6,null,1;
insert into b select 18,5;
insert into b select 19,5;
insert into b select 25,6;
update a 
join (
  select *,
    row_number() over(partition by subjectid order by groupid) _groupid
  from b
)b on a.subjectid=b.subjectid and a._groupid=b._groupid
set a.groupid=b.groupid


select * from a


subjectid
groupid
_groupid


5
18
1

5
19
2

6
25
1