MySQL 8.0

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

create table a (subjectID int, typeID int, groupID int, _groupID int);
insert into a select 5,1,null,1;
insert into a select 5,1,null,2;
insert into a select 5,2,null,1;
insert into a select 5,2,null,2;
insert into a select 6,2,null,1;

create table b (groupID int, teachingTypeID int, subjectID int);
insert into b select 18,1,5;
insert into b select 19,1,5;
insert into b select 20,2,5;
insert into b select 21,2,5;
insert into b select 25,2,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
typeID
groupID
_groupID


5
1
18
1

5
1
19
2

5
2
18
1

5
2
19
2

6
2
25
1