How to update column coming from TOP 1 of another table

user_844247 0
Viewed 27571

I have 2 tables:

  • City table - columns CityID, Name, Period
  • Assets table - columns AssetID, Name

I have to update the Period column of the City table with AssetID of the Assets table matching with the top 1 where City.Name=Assets.Name. The Assets table have identical names for different assets.

Example Assets table:

AssetID  Name
1        Asset1
2        Asset1
3        Asset2
4        Asset2

How can I do this? I tried with different queries but I am not able to get it.

sql-server sql
2 Answers

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
user_10478930
Related