mysql中update一个表的字段时，该字段的值需要从另一个表中select来时的解决办法

如果update一个表的字段时，该字段的值需要从另一个表中select来时，此时需要连表更新，mysql的语法如下：

update oss_system_user a inner join (select id, refGroupId from oss_system_department) b on a.department = b.id set a.departmentGroupId = b.refGroupId;

常规思维这样写是不正确的：

update oss_system_user t set t.departmentGroupId = (select b.refGroupId from oss_system_user a join oss_system_department b on a.department = b.id where a.loginId = 'admin') where t.loginId = 'admin';