SQL 如何更新来自另一个表的 TOP 1 的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6952277/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to update column coming from TOP 1 of another table
提问by Avinash
I have 2 tables:
我有2张桌子：
Citytable - columnsCityID,Name,PeriodAssetstable - columnsAssetID,Name
City表-列CityID，Name，PeriodAssets表 - 列AssetID，Name
I have to update the Periodcolumn of the Citytable with AssetIDof the Assetstable matching with the top 1 where City.Name=Assets.Name. The Assetstable have identical names for different assets.
我必须更新Period的列City与表AssetID的的Assets与顶部1，其中表匹配City.Name=Assets.Name。Assets对于不同的资产，该表具有相同的名称。
Example Assetstable:
示例Assets表：
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.
我怎样才能做到这一点？我尝试了不同的查询，但我无法得到它。
回答by bniwredyc
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;
回答by AndyS
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
