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