How to update column coming from TOP 1 of another table

Question

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.

Answer 1

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;