How to update column coming from TOP 1 of another table
UPDATE City
SET Period = a.AssetID
FROM (SELECT TOP 1 AssetID, Name FROM Assets ORDER BY AssetID ASC) AS a
WHERE City.Name = a.Name;
This should work:
update City
set Period = (
select top 1 a.AssetID
from Assets a
where City.Name = a.Name
order by a.AssetId asc)
Sample code to test:
create table #City (CityId varchar(20), [Name] varchar(20), Period varchar(20))
create table #Assets (AssetId varchar(20), [Name] varchar(20))
insert into #City values (1, 'Asset1', null)
insert into #City values (2, 'Asset2', null)
insert into #City values (3, 'Asset3', null)
insert into #Assets values (1, 'Asset1')
insert into #Assets values (2, 'Asset1')
insert into #Assets values (3, 'Asset1')
insert into #Assets values (4, 'Asset2')
insert into #Assets values (5, 'Asset2')
insert into #Assets values (6, 'Asset3')
insert into #Assets values (7, 'Asset3')
select * from #City
select * from #Assets
update #City
set Period = (
select top 1 a.AssetID
from #Assets a
where #City.Name = a.Name
order by a.AssetId asc)
select * from #City
drop table #City
drop table #Assets