By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3799277 fiddles created (41686 in the last week).
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;
✓
✓
✓
✓
✓
✓
✓
✓
✓
✓
✓
✓
hidden batch(es)
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
