update set from 常用格式:
---不使用别名
UPDATE A SET A1 = B1, A2 = B2, A3 = B3
FROM A
LEFT JOIN B ON A.ID = B.ID
--使用别名
UPDATE v1 SET A1 = B1, A2 = B2, A3 = B3
FROM A as v1
LEFT JOIN B ON v1.ID = B.ID
一、批量更新操作,数据来自其他表的列或本表的其他列
注:
1.SqlServer update语句后面的table名称,是不可以启用别名的。
2.对应不同表的字段更新可以如下:
update Member_Info set MemberLevelName=(
select LevelName from Sys_Level
where MemberLevelId=LevelId);
go
二、update set from 批量更新实例
1.不同表的修改
update Member_Info set MemberLevelName=L.LevelName
from Member_Info
inner join Sys_Level L
on MemberLevelId=L.LevelId
2.表的自连接更新
update v1
set RecommendName=v1.NickName
from Member_Info as v1
inner join Member_Info as v2 on v1.MemberId=v2.RecommendId
3.关联表先汇总,然后更新字段
update t1
set InterestFreeze-=Amount,InterestThaw+=Amount
from Member_Info t1
inner join (
select MemberID,sum(Amount) as Amount from Member_Interest
where Type=1 and DaySum=10
group by MemberID
) t2
on t1.MemberId=t2.MemberID
更多: