目录
1.更新单个字段(子查询)
UPDATE 表A set 字段A = (select 字段B from 表B where B.外键 = A.外键);
# eg:
UPDATE A计划库 set 部门id = (select id from sys_d_department where dept_name = 部门);
mssql更新语句为:
update 表A set [字段A] = B.Id from 表B B where B.TypeName = BridgeLevel;
2.更新字段1和字段2(LEFT JOIN)
UPDATE table_b AS b
LEFT JOIN table_a AS a
ON b.id= a.id
SET b.field_1 = a.field_1 , b.field_2 = a.field_2 ;
# eg:
update BMS_D_PavIndex set SegCode = B.SegmentCode from BMS_D_PavIndex A
left join RMS_D_Segment B on A.RouteCode = B.RouteCode and A.BeginMile = B.BeginMile
where B.SegmentCode like '%A%'
;
运用concat()实现模糊查询
mssql更新语句为:
update 表A set 字段A = 字段B from 表A left join 表B on 表A.外键 = 表B.外键 and A.name like concat(B.name, '%');
3.通过 INNER JOIN,可以更新关联的多张表
update 表1
inner join 表2
on 表1.列名 = 表2.列名
set 表1.列名 = 值 ;
4.通过where更新(可以更新多张表)
update 表1, 表2
set 表1.列名 = 表2.列名
where 表1.列名 = 表2.列名
5.mssql 跨服务器联表更新
【工具】:SQL Server 2014
新建链接服务器后,可通过[服务器名].[数据库名].[表名]的方式对表进行操作。
update [数据库A].[表A] set TypeId = B.typeId
from [数据库A].[表A] A
inner join (
select t_type.Id as typeId, t_asset.Id as Id from [服务器B].[数据库A].[表A] t_asset
left join [服务器B].[数据库A].[表B] t_bridge
on t_asset.Id = t_bridge.AssetId
left join BMS_S_AssetType t_type
on t_type.TypeName = t_bridge.BridgeLevel
where t_asset.Id in (select AssetId from [服务器B].[数据库A].[表B]) ) B
on A.SourceId = B.Id where A.SourceData = '240' ;