update left join在MySQL和Sql Server中的用法
例如有两个表:user(id, uname, score), student(id, surname, personal_name, score)。现在需要将student表中的学生surname和personal_name拼接后与user的uname匹配,然后修改user表中对应人员的分数情况。实际使用中是设计到了子查询,本例可能并不太适合
(1)在MySQL中,可以直接使用:
update user a left join student b
on a.uname = CONCAT(b.surname,b.personal_name)
set a.score = b.score
where a.score is null;
运行后user表数据如下:
(2)在sql server中,上述语句并不能正常运行,进行了如下修改:
update user set score = b.score from user A
left join student B
on A.uname = CONCAT(b.surname,b.personal_name)
left join student B
on A.uname = CONCAT(b.surname,b.personal_name)
where a.score is null;